Microsoft SQL Server 2012 Bible

Multiple-Trigger Interaction

Multiple triggers can become disorganized and extremely difficult to troubleshoot unless they are carefully planned and their effects understood.

Trigger Organization

It is recommended that triggers be organized not by table event, but by the trigger's task, including the following:

• Data validation
• Complex business rules
• Audit trail
• Modified date
• Complex security

Nested Triggers

Trigger nesting refers to whether a DML trigger can cause another trigger to fire. For example, if the Nested Triggers server option is enabled, and a trigger updates TableA, and TableA also has a trigger, then any triggers on TableA can also fire, as shown in Figure 36.2.

Figure 36.2 The Nested Triggers configuration option enables a DML statement within a trigger to fire additional triggers.

By default, the Nested Triggers option is enabled. Use the following configuration command to disable trigger nesting:

EXEC sp_configure ‘Nested Triggers', 0;
RECONFIGURE;

SQL Server triggers have a limit of 32 levels of recursion and generate a fatal error when exceeded. You can test the trigger's nesting level with the Trigger_NestLevel() function to avoid this condition.

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 trigger (see Figure 36.3). If the database ...