April 28, 2011 at 11:53 am
Krasavita,
You should check your database options to ensure that recursive triggers aren't enabled. When recursive triggers are enabled an update statement in the trigger on the same table will cause the same trigger to fire again and you'll get into a nested loop of the trigger firing itself again and again.
In SSMS right-click the database and select properties. Select Options and scroll down to Miscellaneous and look at Recursive Triggers Enabled. In most cases this should be set to False (I've never come across a situation where I would want it to be True). This prevents direct recursion - meaning the trigger can't fire itself by doing an update to the same table.
Indirect recursion can be caused by an update statement in another trigger on the same table. Let's say you have 3 different triggers on the Employee table and they each do an update on the Employee table for different conditions. Trigger 1 will fire triggers 2 and 3 due to the update. Trigger 2 will fire triggers 1 and 3 and so forth. This is called indirect recursion.
Indirect recursion can be detected and prevented by putting this at the top of your trigger code:
IF TRIGGER_NESTLEVEL() > 1
RETURN
However, if a trigger on another related table - let's say you have a table of salesmen and it's related to the employee table - and that trigger does an update to the employee table, the trigger nest level will be 2 and the trigger on the employee table will detect that return without any further processing. This may be something you want and may not. You will have to determine that.
Todd Fifield
May 24, 2011 at 1:44 pm
For peace of mind, on SQL Server Management studio, right click on the database - Properties - Options - Recursive Triggers Enabled ... Set the value to false. And forget that this error ever happend.. :w00t:
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply