Blog of Denis VOITURON

for a better .NET world

SQL Server Nested Triggers

Posted on 2010-10-27

This is my first post in English… so ;-)

In our current project, we developed many SQL Triggers (on each tables). We diagnosed than when a trigger execute a SQL command to update a second table, the second trigger is also fire.

This is because SQL Server uses two parameters to set how triggers are fire :

In our project, we would like “Nested Triggers Mode” on some triggers (and not recursive), but not all triggers. For that, we can evaluate the TRIGGER_NESTLEVEL function. This function returns the number of triggers executed for the statement that fired the trigger. With this function result (more than 1), we allow to execute the end of the trigger or not.

Example : After creating of two table TEST1 and TEST2 (with only one column), check this SQL Triggers code and insert some values in table TEST1 and TEST2.

Remarks: you can get the nesting level of the current stored procedure or trigger execution. Click here for more information.



Follow me

Recent posts