Cascade Update & Delete

Question

Cascade Update & Delete

sani

SSCrazy

Points: 2477
More actions
November 30, 2004 at 12:11 am

#87202

Hi all,
Is it possible to cascade update & delete on a single table? If so how can I do this?
thanks in advance.
Sani

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Can you please provide more information? I think this is possible but without knowing what you are attempting to accomplish it is kind of hard.

You can build loops that perform update/delete ....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Ruud Boots Old Hand Points: 320 More actions · Answer 2

try using triggers, one for update, one for delete

Ruud

sani SSCrazy Points: 2477 More actions · Answer 3

Tanx for the replies.

I do the following step and now It works fine:

1:

ALTER DATABASE [cascade] SET RECURSIVE_TRIGGERS ON

2:

CREATE TRIGGER

mytriger ON [dbo].[t4]

FOR

DELETE

AS

IF

@@rowcount = 0 RETURN

DELETE FROM

T

FROM

t4 AS T JOIN deleted AS D

ON T.parent_id = D.[id]

esmith21 SSC Journeyman Points: 87 More actions · Answer 4

One other option is to set the cascading on the foreign key itself. You can optionally set ON DELETE or ON UPDATE after the references section when defining a foreign key. When updating or deleting the parent row, you will automatically delete/update the child row.

This will work on a single table with self-referencing fks, but I've found it very useful if you have a large model and need to delete a top level row and all of it's children, but don't feel like starting at the bottom of the tree and working your way up.

Sytax is something like:

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY

(child_id) REFERENCES [parent_table_name]

(parent_id) ON DELETE CASCADE

Check BOL for more information.