brettstahlman (3/14/2013)
Thanks. Hopefully someone from the SQL Server team will weigh in on this, as it appears there may be an error in the documentation/spec. I started to call it an ambiguity, but the documentation states unambiguously that RECURSIVE_TRIGGERS can't be used to prevent indirect recursion, and the case I've described qualifies as indirect recursion, according to the definition provided in the documentation.
I am not convinced your use case qualifies as indirect recursion since the action in the AFTER trigger does not cause the INSTEAD trigger to fire. If it did, and that inturn caused the AFTER trigger to fire again I still do not think it would qualify an indirect. Here is why.
From http://msdn.microsoft.com/en-us/library/ms190739(v=sql.105).aspx:
Indirect recursion
This recursion occurs when a trigger fires and performs an action that causes another trigger of the same type (AFTER or INSTEAD OF) to fire. This second trigger performs an action that causes the original trigger to fire again. In other words, indirect recursion can occur when an INSTEAD OF trigger is called for a second time, but not until another INSTEAD OF trigger is called in between. Likewise, indirect recursion can occur when an AFTER trigger is called for a second time, but not until another AFTER trigger is called in between. For example, an application updates table T1. This update causes AFTER trigger Trig1 to fire. Trig1 updates table T2, and this update causes AFTER trigger Trig2 to fire. Trig2 in turn updates table T1 that causes AFTER trigger Trig1 to fire again.
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
I think this is a matter of entry point. It seems that when the entry point to the chain of triggers firing is an INSTEAD trigger direct recursion is in play. But if the first trigger to fire is an AFTER trigger then it cannot initiate direct recursion when the next trigger in the chain would be an INSTEAD trigger. I agree this is an odd distinction in the behavior and I cannot find any documentation to explain the effect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato