Cascading Triggers not firing?

Cascading Triggers not firing?

Post by Alkemys » Wed, 24 Jan 2001 03:56:30



Under MSSQL2k, a delete trigger which should cause another delete
trigger to the same table does not fire.

Here is my code:

CREATE TRIGGER [Remove_Folders] ON [dbo].[folder]
FOR DELETE
AS
BEGIN
 DELETE [dbo].[folder] FROM [dbo].[folder] INNER JOIN DELETED ON [dbo].
[folder].ParentID = DELETED.PathID
END

dbo.folder is a table which contains two columns: ParentID, PathID in
which ParentID and PathID are a parent-child relationship and when any
PathID row is deleted so will the child rows be with this trigger.

Thanks

Chris

Sent via Deja.com
http://www.deja.com/

 
 
 

Cascading Triggers not firing?

Post by Itzik Ben-Ga » Wed, 24 Jan 2001 08:20:32


In order for the trigger to work as you wish, you need to enable recursive
triggers in your database. This option is false by default:

EXEC sp_dboption <db_name>, 'recursive triggers', true
GO

Also, make sure you add a recursion termination check. Triggers fire per
action, whether the action involves one, several, or even zero rows. By not
including a recursion termination check, any delete attempt will cause the
trigger to nest 32 levels and than...all is rewind, or more accurately,
rolled back.

CREATE TRIGGER Remove_Folders ON dbo.folder
FOR DELETE
AS
BEGIN
  -- recursion termination check
  IF EXISTS(SELECT *
            FROM dbo.folder JOIN deleted
            ON dbo.folder.ParentID = deleted.PathID)
    -- delete child nodes
    DELETE dbo.folder
    FROM dbo.folder JOIN deleted
      ON dbo.folder.ParentID = deleted.PathID
END
GO

--
BG

Hi-Tech College, Israel
http://sql.hi-tech.co.il


Quote:> Under MSSQL2k, a delete trigger which should cause another delete
> trigger to the same table does not fire.

> Here is my code:

> CREATE TRIGGER [Remove_Folders] ON [dbo].[folder]
> FOR DELETE
> AS
> BEGIN
>  DELETE [dbo].[folder] FROM [dbo].[folder] INNER JOIN DELETED ON [dbo].
> [folder].ParentID = DELETED.PathID
> END

> dbo.folder is a table which contains two columns: ParentID, PathID in
> which ParentID and PathID are a parent-child relationship and when any
> PathID row is deleted so will the child rows be with this trigger.

> Thanks

> Chris

> Sent via Deja.com
> http://www.deja.com/


 
 
 

Cascading Triggers not firing?

Post by Alkemys » Thu, 25 Jan 2001 03:29:18


That did it!  I could not find this documented anywhere.

Thanks
Chris



Quote:> In order for the trigger to work as you wish, you need to enable
recursive
> triggers in your database. This option is false by default:

> EXEC sp_dboption <db_name>, 'recursive triggers', true
> GO

> Also, make sure you add a recursion termination check. Triggers fire
per
> action, whether the action involves one, several, or even zero rows.
By not
> including a recursion termination check, any delete attempt will
cause the
> trigger to nest 32 levels and than...all is rewind, or more
accurately,
> rolled back.

> CREATE TRIGGER Remove_Folders ON dbo.folder
> FOR DELETE
> AS
> BEGIN
>   -- recursion termination check
>   IF EXISTS(SELECT *
>             FROM dbo.folder JOIN deleted
>             ON dbo.folder.ParentID = deleted.PathID)
>     -- delete child nodes
>     DELETE dbo.folder
>     FROM dbo.folder JOIN deleted
>       ON dbo.folder.ParentID = deleted.PathID
> END
> GO

Sent via Deja.com
http://www.deja.com/
 
 
 

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

I have 2 tables (A & B) B has a field that has a foreign key constriant
tied to a column in A. That foreign key has ON DELETE CASCADE. When the
record in A is deleted, causing the ON DELETE CASCADE to occur in B,
does a DELETE trigger for B get fired (I have a delete trigger for B)?

Thanks

Darin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Cloudscape Newsgroups ??

3. Replication and Triggers...Subscriber Trigger not firing??

4. insertrecord

5. Trigger not firing on SQL Server

6. No Btrieve access under VB 4.0 32bit ??

7. bulk insert not firing insert triggers

8. VB users group in Hawaii

9. INSTEAD OF triggers on View not firing with DTS

10. HELP!!! - Trigger not firing

11. Trigger Not Firing as Expected:

12. BUG: delete trigger is not fired always

13. Triggers not firing periodically