asked on

recursive trigger does not work.

Hello,

I have one table that point to themselve.
the PK is n_id and the FK is n_parent_id.

I have a trigger on that table after delete statement.
When a node is deleted I also delete childnode but if the child node have childnode I also need to delete them.

The trigger works fine for the first delete but if the first child have childnode the tigger is not fired.

I set the nested_trigger to 1 with sp_configure and when I execute sp_dboptions I see : "recursive triggers"

Did I forgot something ? Can you help me

ALTER TRIGGER [dbo].[OnNodeDelete] 
   ON  [dbo].[syn_nodes] 
AFTER DELETE
as
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    declare @deleted_node_id int
    select @deleted_node_id = n_id from deleted
   
    delete from syn_nodes where syn_nodes.n_parent_id = @deleted_node_id //shoul trigger the samme trigger but does not
 
 
END

Open in new window

BrandonGalderisi

First question. Do you have recursive triggers enabled? Secondly.... if you delete more than one record at a time, your select @deleted_note_id = n_id from deleted will NOT WORK.

ALTER TRIGGER [dbo].[OnNodeDelete] 
   ON  [dbo].[syn_nodes] 
AFTER DELETE
as
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
 
delete from sn
from syn_nodes snParent
join deleted d 
on sn.parent.n_parent_id = d.n_id
 
-- But you HAVE to enable recursive triggers.
 
 
END

Open in new window

BrandonGalderisi

Run this and it will tell you whether or not it is currently enabled.

exec sp_dboption '{YOUR DATABASE NAME}', 'recursive triggers'

Open in new window

brejk

ALTER DATABASE YourDatabaseName SET RECURSIVE_TRIGGERS ON
-- and be careful with your triggers and their @@NESTLEVEL!!!

PHD

ASKER

you are right : recursive trigger is off
How can I set it to on ?

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PHD

ASKER

ok but when I run this statement : delete from nodes where n_id=77

this return : Msg 217, Level 16, State 1, Procedure OnNodeDelete, Line 20
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

But node 77 has two child that have respectedly 1 child.

Is it something with @@nestedlevel ?
I also plan to use a cursor for multiple delete, is it right ?

thank you all

BrandonGalderisi

Run the below query. How many records are returned? Now for each of the records in snParent, run the script again replacing the 77 with each n_ID. Recursive means that it will delete the whole way down the tree.

select *
from syn_nodes snParent
join syn_nodes d 
on sn.parent.n_parent_id = d.n_id
where d.n_id = 77

Open in new window

PHD

ASKER

one record returned with 77 (78)

then 2 records with 78 (79,80)

0 record with 79 and 80.

I expect when I delete 77, all the bottom tree structure is also deleted (in cascade)

BrandonGalderisi

It may be sticking in the recursive trigger even if there are no deleted records. Try adding a return if @@rowcount=0 like I did below. That can stop the recursion if it's the problem.

ALTER TRIGGER [dbo].[OnNodeDelete] 
   ON  [dbo].[syn_nodes] 
AFTER DELETE
as
BEGIN
if @@Rowcount=0
   return
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
 
delete from sn
from syn_nodes snParent
join deleted d 
on sn.parent.n_parent_id = d.n_id
 
-- But you HAVE to enable recursive triggers.
 
 
END

Open in new window

PHD

ASKER

thannk you, IT is working but I don't understand why I need to add the if @@rowcount.

Do you know why ?

BrandonGalderisi

Even if there are no records effected by an update statement, the trigger still fires. When the trigger fires, it runs the join to deleted again which returns ZERO records in the first recursive update. But, as previously stated, it doesn't take records being updated for a trigger to fire. So it keeps going unless we tell it not to.

As a test, run the following statement

use tempdb
go
create table t (t int)
go
create trigger t_trigg on t for insert,update,delete
as
print 'hello'
go
update t
set t=0 
where 1=0
go
drop table t
 
 
-- Then try this
use tempdb
go
create table t (t int)
go
create trigger t_trigg on t for insert,update,delete
as
if @@rowcount=0
 return
 
print 'hello'
go
update t
set t=0 
where 1=0
go
drop table t

Open in new window

PHD

ASKER

Thank you BrandonGalderisi.
I don't know you but I'm sure that you are a great guy.

I will post another question in 5 minutes then I will make a link here as coment.
So If are interrested ....