Board index MS SQL Server

HELP! Creating delete trigger for cascade delete

HELP! Creating delete trigger for cascade delete

Post by Pete » Tue, 07 Mar 2000 04:00:00



sorry - newbie question.  migrating my db design from oracle SQL Server 7 so
i'm trying to replace all my cascade delete foreign key definitions with
cascade delete triggers.  i can create my trigger fine:

CREATE TRIGGER space.sitefiles_delete ON space.files
FOR DELETE
AS
DELETE space.sitefiles
FROM space.sitefiles, deleted
WHERE space.sitefiles.fileid = deleted.fileid

but it never seems to fire.  i get a foreign key constraint error when i try
to delete a referenced record:

DELETE statement conflicted with COLUMN REFERENCE constraint .....

i don't think my trigger is firing at all.  what am i missing - please help!

thanks
pete

 
 
 
Top

HELP! Creating delete trigger for cascade delete

Post by Rainer Schwenkrei » Tue, 07 Mar 2000 04:00:00


Hello Pete,



Quote:> sorry - newbie question.  migrating my db design from oracle SQL Server 7
so
> i'm trying to replace all my cascade delete foreign key definitions with
> cascade delete triggers.

There is no Way to do a Cascading Delete in SQL Server 7.0 also Triggers
wont work.

You can however omit the References Clause in the Table definition and then
your Trigger
will work, but on the other side you will loose the referential integrity of
your Database.

 
 
 
Top

HELP! Creating delete trigger for cascade delete

Post by BPMargoli » Tue, 07 Mar 2000 04:00:00


Pete,

As Rainer has posted, you have to choose between DRI (i.e., REFERENCE in the
table schema) and triggers. SQL Server 7.0 checks the DRI before attempting to
execute the trigger, so DRI and cascading triggers become mutually exclusive.

SS2K will support cascading updates and deletes via DRI as specified in ANSI
SQL-92.


> sorry - newbie question.  migrating my db design from oracle SQL Server 7 so
> i'm trying to replace all my cascade delete foreign key definitions with
> cascade delete triggers.  i can create my trigger fine:

> CREATE TRIGGER space.sitefiles_delete ON space.files
> FOR DELETE
> AS
> DELETE space.sitefiles
> FROM space.sitefiles, deleted
> WHERE space.sitefiles.fileid = deleted.fileid

> but it never seems to fire.  i get a foreign key constraint error when i try
> to delete a referenced record:

> DELETE statement conflicted with COLUMN REFERENCE constraint .....

> i don't think my trigger is firing at all.  what am i missing - please help!

> thanks
> pete

 
 
 
Top

1. delete trigger or cascade delete, which one??

Hi,

I am working on a project that uses a SQL2000 DB and everything has been set
up already. However I now need to look at/change some things, like triggers
and the like.

In one case I have found the following relationship:
        TableA (primary) ------> TableB (foreign).

A delete trigger set up for TableA says delete all records from TableB where
the associate IDs match.

My question is since this delete trigger is doing a simply child delete
would it not be better to enable the cascade delete option on the
relationship?

Is there any difference between a simply delete in a trigger and cascade
delete?
What is best practice?

Regards,
Peter Row

2. Why did I buy Delphi?

3. No INSTEAD OF DELETE triggers for tables with ON DELETE CASCADE constraints

4. select null ?

5. ON DELETE CASCADE - does it fire delete trigger?

6. SQL Anywhere & Powerbuilder 4.0

7. Delete Cascade & Delete Triggers

8. oracle80 archive problem

9. Creating a Cascade Delete Trigger

10. creating a trigger to support delete cascade

11. help with trigger that makes cascade delete

12. delete cascade triggers, help!

13. Help on Triggers (Cascading deletes)


All times are UTC
Board index