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

LVL 6
PHDAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
this method will work... probably brejk's too.
exec sp_dboption '{YOUR DATABASE NAME}', 'recursive triggers', 'on'

Open in new window

0
 
BrandonGalderisiCommented:
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

0
 
BrandonGalderisiCommented:
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

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
brejkCommented:
ALTER DATABASE YourDatabaseName SET RECURSIVE_TRIGGERS ON
-- and be careful with your triggers and their @@NESTLEVEL!!!
0
 
PHDAuthor Commented:
you are right : recursive  trigger is off
How can I set it to on ?
0
 
PHDAuthor Commented:
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
0
 
BrandonGalderisiCommented:
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

0
 
PHDAuthor Commented:
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)
0
 
BrandonGalderisiCommented:
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

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

Do you know why ?
0
 
BrandonGalderisiCommented:
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

0
 
PHDAuthor Commented:
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 ....
0
All Courses

From novice to tech pro — start learning today.