FWIW, I'm told by our DBA that SQL Server 2000 has a setting that allows or
disallows recursive trigger execution. When disallowed, triggers apparently
just don't recursively call themselves, even if they are designed to do so,
i.e. they won't generate an error. When allowing recursion, he's not sure
whether there's a limit, some internal checking that attempts to discern
when reasonableness has been exceeded or if it will just merrily recurse
away until stopped. And he's not willing to test it. :-)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ tsqlref/ts_create2_7eeq.asp
Nested Triggers
Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows nested triggers. If nested triggers is off, recursive triggers is also disabled, regardless of the recursive triggers setting of sp_dboption.
---------
I think Oracle has a maximum level of 50. But I have no way to test this.
Seems like people have been doing OK with 0 levels of recursion, so IMHO an arbitrary stack limit which can be adjusted with a pragma would be the best/simplest solution. Trying to figure out if the user is getting into trouble using recursion would probably be a fruitless endeavor. (But, again that is just my opinion.)
-Charlie
