Multiple triggers can become disorganized and extremely difficult to troubleshoot unless they are carefully planned and their effects understood.
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
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.
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.
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 ...