Microsoft® SQL Server 2008 R2 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:

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

After the nested triggers option is turned off, the only triggers to fire are those ...