Run this and it will tell you whether or not it is currently enabled.
Main Topics
Browse All TopicsHello,
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
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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
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
Business Accounts
Answer for Membership
by: BrandonGalderisiPosted on 2008-01-29 at 06:12:31ID: 20767858
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.
Select allOpen in new window