SQL Server Nested Triggers

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 :

• RECURSIVE TRIGGERS : a database flag. This recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again.
• NESTED TRIGGERS : a server flag. This recursion occurs when a trigger fires and performs an action that causes another trigger to 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.