Trigger Nesting

Triggers can be nested (in an arrangement also described as a trigger cascade). In other words, a trigger on TableA can update TableB that fires a trigger on TableC that fires a trigger on TableD that…. SQL Server will prevent a chain of triggers from forming an infinite loop, and you cannot nest to more than 32 levels.

You can also disable nested trigger execution, on a server-wide basis, using the sp_configure stored procedure. The default is that trigger nesting is allowed. Nesting and recursion are controlled by the same argument in sp_configure, so if you turn off nesting, recursion goes as well, and vice versa; this is regardless of the setting you have used in the recursion attribute set by sp_dboption, discussed in the section on recursion. Trigger nesting also terminates if any trigger executes a ROLLBACK TRANSACTION statement.

You can also manage the nesting behavior of triggers from Management Studio or from the SQL-DMO object model or using the sp_configure procedure (see Chapter 8).