Trigger Recursion

SQL Server 2005 provides a feature known as recursive invocation. Recursion can thus be invoked on two levels, indirect recursion and direct recursion. The two types permit the following behaviors:

• Indirect   A statement triggers TableA, Trigger1, which causes an event that fires TableB, Trigger1. TableB, Trigger1 then causes TableA, Trigger1 to fire again.

• Direct   A statement triggers TableA, Trigger1, which causes an event that fires TableA, Trigger2. TableA, Trigger2 then causes TableA, Trigger1 to fire again.

The recursion types can work for or against you and can break your code. You can set direct recursion off using the sp_dboption stored procedure, but that will leave indirect recursion enabled, which you may want. To disable both recursion types, you need to use sp_configure.