> CREATE TRIGGER deep BEFORE DELETE ON list BEGIN > DELETE FROM list WHERE id=old.next; > END; > > This trigger is guaranteed to terminate because it will > eventually run out of entries to delete. But given any > recursion limit, we can always construct a case where > the trigger will want to run longer than the limit. > > So do we put recursion limits on UPDATE and INSERT triggers > only and let DELETE triggers run as long as they like? > That works as far as I know, but it seems kind of arbitrary.
Under what circumstances might one *want* to update or insert recursively? What circumstances that can't be handled by a properly constructed query or set of queries? I can see the delete, but even that can be handled another way. I'm aware of the advantages of triggers, and the absence of stored procedures makes them even more valuable, but I'm curious. > Surely somebody else has been down this road before me and > can offer a little guidance? 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. :-)
