894
Part VII: Monitoring and Auditing
A trigger is considered recursive only if it directly fi res itself. If the trigger executes a
stored procedure that then updates the trigger’s table, then that is an indirect recursive
call, which is not covered by the recursive-trigger database option.Recursive triggers are enabled with ALTER DATABASE:ALTER DATABASE DatabaseName SET RECURSIVE_TRIGGERS ON | OFF ;
Practically speaking, recursive triggers are rare.One example that involves recursion is a ModifiedDate trigger. This trigger writes
the current date and time to the modifi ed column for any row that’s updated. Using the
AdventureWorks2012sample database, this script fi rst adds a Created and Modified col-
umn to the department table:USE AdventureWorks2012;ALTER TABLE [HumanResources].[Department]
ADD
Created SmallDateTime NOT NULL DEFAULT CURRENT_TIMESTAMP,
Modified SmallDateTime NOT NULL DEFAULT CURRENT_TIMESTAMP;If recursive triggers are enabled, then this trigger might become a runaway trigger, and
after 32 levels of recursion will error out.The trigger in the following example prints the Trigger_NestLevel() level. This is help-
ful for debugging nested or recursive triggers, but it should be removed when testing has
fi nished. The second if statement prevents the Created and Modified date from being
directly updated by the user. If the trigger is fi red by a user, then the nest level is 1.The fi rst time the trigger is executed, the UPDATE is executed. Any subsequent executions
of the trigger RETURN because the trigger nest level is greater than 1. This prevents run-
away recursion. Here’s the trigger DDL code:
CREATE TRIGGER Products_ModifiedDate ON Production.Product
AFTER UPDATE
AS
IF @@ROWCOUNT = 0
RETURN;If Trigger_NestLevel() > 1
Return;SET NOCOUNT ON;PRINT TRIGGER_NESTLEVEL();If (UPDATE(ModifiedDate))Begin;c36.indd 894c36.indd 894 7/31/2012 10:03:09 AM7/31/2012 10:03:09 AM
http://www.it-ebooks.info