Microsoft SQL Server 2014 Unleashed

Using Nested Triggers

Triggers can be nested up to 32 levels. If a trigger modifies records in a table on which another trigger exists, the trigger on the second table is fired. If the second trigger modifies data in another table that also has a trigger, the trigger on the third table will fire, and so on. If the nesting level is exceeded, the entire operation is canceled, and the transaction is rolled back.

The following error message is returned if the nesting level is exceeded:

Click here to view code image

Server: Msg 217, Level 16, State 1, Procedure ttt2, Line 2Maximum stored procedure nesting level exceeded (limit 32).

By default, nested triggers in SQL Server are enabled. You can disable nested triggers by setting the nested triggers ...