Presents your 
SQL SERVER E-NEWSLETTER for July 29, 2003

<------------------------------------------->

POINTERS ON USING RECURSIVE TRIGGERS

Even though recursive triggers are complex and hard to manage, they can
come in quite handy. We'll offer you a brief description of recursive
triggers and then provide you with a number of helpful tips to keep in 
mind when using them.

Recursive triggers activate other triggers either in the original table
or in another table. The two types of recursions for triggers are direct
and indirect. A direct recursion occurs when a trigger fires and its own
action causes the same trigger to fire again. An indirect recursion
occurs when a trigger fires and activates a trigger on another table.

WHAT TO KEEP IN MIND WHEN USING RECURSIVE TRIGGERS

* Trigger recursion is limited to 32 levels. If a trigger is caught in
an endless loop and exceeds the 32 levels, the trigger terminates and 
the transaction rolls back.

* If any trigger does a ROLLBACK TRANSACTION, no further triggers will
execute (regardless of the trigger's nesting level).

* Test your recursive triggers rigorously. Untested recursive triggers
can do a lot of damage to your data.

* A trigger that exceeds the 32-level limit is a moot trigger. So
consider including a controlled logic loop or other termination check, 
which protects your recursive trigger from becoming invalid.

* You can't control the point at which the initial modification sets off
the trigger series, so if your tables must be updated in a specific
order, you can't use recursive triggers. You can only set first and 
last triggers; all of the triggers in between are at the discretion of SQL 
Server.

* Indirect recursion is ON by default. To turn it OFF, you must set the
nested triggers server option to 0 using sp_configure.

* Direct recursion is OFF by default. You must turn it ON by using the
ALTER DATABASE statement. The example below turns on direct recursion 
for the dbName database.

USE master
GO
ALTER DATABASE dbName
SET RECURSIVE_TRIGGERS ON
GO


J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about
technology for more than two decades.

----------------------------------------