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:
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
EXEC sp_configure 'nested triggers', 0GORECONFIGURE WITH OVERRIDEGO
After the nested triggers option is turned ...