Triggers - Pro SQL Server Internals - page 174

Nested and Recursive Triggers
Both DDL and DML triggers are nested when their actions fire the triggers in the other tables. For example, you can
have an AFTER UPDATE trigger on Table A that updates Table B, which has its own AFTER UPDATE trigger defined.
When nested triggers are enabled, the trigger on Table B would be fired. You can control that behavior by setting
the nested trigger server configuration option. The code in Listing 8-17 disables the nested trigger execution.

Listing 8-17. Disabling nested triggers

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'nested triggers', 0 ;
GO
RECONFIGURE;
GO

By default, nested trigger execution is enabled. In the case of infinite loops, SQL Server terminates the execution
and rolls back the transaction when the nesting level exceeds 32.
Another database option, recursive_triggers , controls if an AFTER trigger can fire itself. There are two types
of recursion. With direct recursion , the trigger fires itself by performing the same action against the table where it has
been defined. For example, when an AFTER UPDATE trigger updates the same table. By default, direct recursion
is disabled. Indirect recursion , on the other hand, happens when Table A performs the action that fires the trigger
in Table B and the trigger on Table B performs the action that fires the same trigger on Table A. To prevent indirect
recursion from happening, we need to disable the nested triggers configuration option on the server level.

■ You need to be careful about changing the nested triggers or recursive_triggers options. Developers often
rely on default trigger behavior, and you can break existing systems by changing those options.

Caution

First and Last Triggers
In a situation where a table has multiple AFTER triggers, you can specify what triggers are firing first and last by using
the sp_settriggerorder system stored procedure. For example, the code in Listing 8-18 makes trg_Data_AUAudit
the first in the execution.

Listing 8-18. Specifying triggers execution order

sp_settriggerorder @triggername = ' trg_Data_AUAudit', @order = 'first', @stmttype = 'UPDATE'

Each action— INSERT , UPDATE , and DELETE —can have its own first and last triggers specified. The value will be
cleared when trigger is altered.
You cannot control the order in which triggers fire in any other way.