893
Chapter 36: Creating Triggers
36
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() func-
tion 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 fi re, then it’s a recursive trigger (see Figure 36-3). If the data-
base recursive triggers option is off, then the recursive iteration of the trigger won’t fi re.
(Note that nested triggers is a server option, whereas recursive triggers is a database option.)FIGURE 36-3
A recursive trigger is a self-referencing trigger — one that executes a DML statement that
causes itself to be fi red again.c36.indd 893c36.indd 893 7/31/2012 10:03:09 AM7/31/2012 10:03:09 AM
http://www.it-ebooks.info