On Fri, 03 Dec 2004 19:04:56 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
SQLite does not currently support recursive triggers. On of the main reasons for not supporting recursive triggers is that disallowing recursive triggers was seen as the easiest way to avoid infinite loops like this:
CREATE TRIGGER loop AFTER UPDATE OF table1 BEGIN UPDATE table1 SET cnt=(cnt+1)%100 WHERE rowid=old.rowid; END; UPDATE table1 SET cnt=1 WHERE rowid=1; -- Infinite loop
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 in SQLite and allow some support for recursive triggers as long as the recursive triggers do not cause an infinite loop. But I'm not sure how to do about it?
Question: What do other RDBMSes do with triggers that form infinite loops? Does anybody know?
Question: Can anybody suggest a way of providing support for recursive triggers which also guarantees that every SQL statement will eventually complete?
Question: Is there any progress on this? I know full and well the evils of recursive triggers and trouble they can bring but my program logic won't allow for any cases where a recursive trigger will run into any problems and users can't insert rows manually to screw stuff up.
Or maybe, is there some way to enable it to simply run recursive triggers? I don't feel I need any protective code or anything, right now I have to add a custom function that gets called in my update triggers which then do an update upward (in a tree) on records which feels like a bad solution to the problem.
Regards, Peter Bartholdsson
