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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
brejkCommented:
ALTER DATABASE YourDatabaseName SET RECURSIVE_TRIGGERS ON
-- and be careful with your triggers and their @@NESTLEVEL!!!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PHDAuthor Commented:
you are right : recursive  trigger is off
How can I set it to on ?
0
BrandonGalderisiCommented:
this method will work... probably brejk's too.
exec sp_dboption '{YOUR DATABASE NAME}', 'recursive triggers', 'on'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.