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 any trigger in the chain sets off an infinite loop, 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:

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

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

 EXEC sp_configure 'nested triggers', 0  GO RECONFIGURE WITH OVERRIDE GO 

After the nested triggers option has been turned off, the only trigger (or triggers) to fire are those that are part of the original data modification: the top-level trigger(s). If updates to other tables are made via the top-level trigger(s), then those updates will be completed but the triggers on those tables will not fire. For example, perhaps you have an UPDATE trigger on the jobs table in the Pubs database and an UPDATE trigger on the employee table as well. The trigger on the jobs table updates the employee table. If an update is made to the jobs table, then the jobs trigger will fire and complete the updates on the employee table. However, the trigger on the employee table will not fire.

The default configuration allows nested triggers.