2018-08-18

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.

4 thoughts on “SQL Server Nested Triggers

  1. Hiya very nice site!! Guy .. Excellent .. Wonderful
    .. I’ll bookmark your website and take the feeds additionally? I am happy to find a lot of helpful information here in the post, we need develop more strategies in this regard, thank you for sharing. . . . . .

Leave a Reply

Your email address will not be published. Required fields are marked *