Microsoft® SQL Server 2012 Unleashed

Using Nested Triggers

Triggers can be nested up to 32 levels. If a trigger changes a table on which another trigger exists, the second trigger is fired and can then fire a third trigger, and so on. If the nesting level is exceeded, the trigger 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).

You can disable nested triggers by setting the nested triggers option of sp_configure to 0 (off):

Click here to view code image

EXEC sp_configure 'nested triggers', 0GORECONFIGURE WITH OVERRIDEGO

After the nested triggers option is turned ...