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.

Tagged with: ,
Posted in SQL Server, Tips and others
4 comments on “SQL Server Nested Triggers
  1. Chiropractor says:

    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. . . . . .

  2. Rosie says:

    Good points all around. Truly apepeciatrd.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow me
   RSS Feed    Twitter    Linked In

Write you email address to subscribe at this blog and to receive new posts by mail.

Join 611 other followers

Who I am?

%d bloggers like this: