What are Nested and Recursive Triggers?

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER.

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers can be nested regardless of this setting.

Nested Triggers - Nesting means, when a trigger fired, it will also cause another trigger to be fired. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. Triggers (DML and DDL) can be nested to a maximum of 32 levels.

If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled with error message.

To disable nested triggers, set nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers.

--stop executing all the nested triggers.

sp_CONFIGURE 'nested_triggers', 0

RECONFIGURE

Recursive Triggers - A recursive trigger is a unique type of nested AFTER trigger. If a trigger executes a DML statement that causes itself to fire, then it's a recursive triggerSQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE. Recursive triggers enable the following types of recursion to occur:

·         Indirect recursion: With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger TR2 then fires and updates table T1.

·         Direct recursion: With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

--Alternate way to stop Trigger Recursion

ALTER DATABASE databasename

SET RECURSIVE_TRIGGERS ON | OFF

Note: Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0. Note that nested triggers is a server option, whereas recursive triggers is a database option.

Note: Triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Include PRINT statements in your triggers so that you can determine where failure has occurred.