February 7, 2005 at 6:46 am
I need to write a INSERT trigger to insert set of records to the same table. will those fire the insert trgger again and will those ended up in a dead lock
My Blog:
February 7, 2005 at 7:25 am
To be sure that it does not happen, disable firing of triggers within the scope of the first tigger.
See BOL for exact code.
Naveen
February 7, 2005 at 7:30 am
Hi,
There's a setting that regulates whether a trigger will be called recursively or not: RECURSIVE_TRIGGERS
If you allow recursive triggers, it will not necessarily end up in a dead-lock; more likely is it that you will reach the maximum number of nesting allowed at the server. But you'll definitely need to watch out for both those situations.
An alternative to the usual insert-triggers (that fire after the insertion and before the final commit), is to write a "INSTEAD OF" trigger. They fire before any attempt is made to modify the data, and you will be able to control exactly which records you want inserted. More help on this on BOL - personally I do not have a lot of experience with them.
Hope this helps,
Olav
February 7, 2005 at 7:32 am
Can also use "SELECT trigger_nestlevel()" if it suits your requirements...
Naveen
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply