On Fri, Dec 03, 2004 at 07:04:56PM -0500, D. Richard Hipp wrote: > By disallowing recursive triggers, SQLite avoids the > infinite loop above. But there are useful things one > could do with recursive triggers that do not involve > infinite loops. I would like to relax the constraint
> Question: What do other RDBMSes do with triggers that > form infinite loops? Does anybody know? Back in 2000, I definitely ran into "interesting" Oracle problems with operations that could cause recursive trigger execution. My stopgap at the time was simply to disallow those operations. Oh, I just found some old notes about it: http://ccm.redhat.com/bboard-archive/webdb/000IQB.html So the actual error I saw was: ORA-04092: cannot SET SAVEPOINT in a trigger That was probably in Oracle 8.1.6 or 8.1.7. Searching Oracle's newer 9i docs for "recursive trigger" gives only two hits: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96525/e0.htm#1002622 http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#7445 The first is simply Oracle admonishing developers, "DO NOT CREATE RECURSIVE TRIGGERS". The second is the error message: ORA-00036 maximum number of recursive SQL levels (string) exceeded Cause: An attempt was made to go more than the specified number of recursive SQL levels. Action: Remove the recursive SQL, possibly a recursive trigger. So this suggests that Oracle certainly does allow recursive triggers (up to some stack limit), but that recursive triggers have various specific - and undocumented - limitations in what they are actually able to do. > Question: Can anybody suggest a way of providing support > for recursive triggers which also guarantees that > every SQL statement will eventually complete? Perhaps by implementing a "production system" as found in logic programming, using the Rete, Rete2, Treat, or LEAPS forward chaining inference algorithms. That would be very cool, but might be out of scope for your current work. :) Here's some more info: http://openacs.org/forums/message-view?message_id=44805 -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
