Re: [sqlite] Recursive triggers
From: "Simon Slavin" Sent: Monday, April 26, 2010 2:31 PM > I don't know the answer to this question, but I have considered it in one > of my > programs. It simply issues a "PRAGMA recursive_triggers = 'on'", then > does a > "PRAGMA recursive_triggers" and looks to see what it gets back. Anything > but > a '1' indicates too early a version of SQLite, so it triggers an error > message and > a quit. That is a good general solution. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive triggers
On 26 Apr 2010, at 11:09am, Andy Gibbs wrote: > I notice in the fossil repository that Sqlite is now moving towards version > 3.7.0. According to the pagehttp://www.sqlite.org/news.html#2009_sep_11, it > is anticipated that recursive triggers will be enabled by default from > version 3.7.0 (cf also http://www.sqlite.org/limits.html#max_trigger_depth). > Please can I simply enquire whether this is still to be expected? I don't know the answer to this question, but I have considered it in one of my programs. It simply issues a "PRAGMA recursive_triggers = 'on'", then does a "PRAGMA recursive_triggers" and looks to see what it gets back. Anything but a '1' indicates too early a version of SQLite, so it triggers an error message and a quit. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
> The same is true of FOREIGN KEY, by the way (I checked), but that's a bit > more obvious since breaking FOREIGN KEY will always result in a database the > programmer would consider corrupt. You're not quite right. You're talking about immediate foreign keys. There're deferred foreign keys too. See section 4.2 here: http://www.sqlite.org/foreignkeys.html. Pavel On Wed, Nov 25, 2009 at 9:13 AM, Simon Slavin wrote: > > On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: > >>> I couldn't find the answer documented anywhere, so I will have to assume >>> that it may change in future versions. Unless the requirement for depth >>> first is somewhere in the SQL specification. >> >> I believe it should be. Triggers should be executed before the >> statement causing them to fire is considered successful. Otherwise all >> triggers checking some constraints and prohibiting incorrect data (and >> thus raise(...) function) are useless. > > Ahha. That makes sense: it would have to RAISE an error and back out the > other transactions anyway, so it may as well do the TRIGGERs first. Okay, on > that basis I can write my code assuming that it will always behave > depth-first. > > The same is true of FOREIGN KEY, by the way (I checked), but that's a bit > more obvious since breaking FOREIGN KEY will always result in a database the > programmer would consider corrupt. I wrote some code in another DBMS once > that betrayed that it did some really nasty things when one FOREIGN KEY > triggered another one. > > Thanks for the help. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote: >> I couldn't find the answer documented anywhere, so I will have to assume >> that it may change in future versions. Unless the requirement for depth >> first is somewhere in the SQL specification. > > I believe it should be. Triggers should be executed before the > statement causing them to fire is considered successful. Otherwise all > triggers checking some constraints and prohibiting incorrect data (and > thus raise(...) function) are useless. Ahha. That makes sense: it would have to RAISE an error and back out the other transactions anyway, so it may as well do the TRIGGERs first. Okay, on that basis I can write my code assuming that it will always behave depth-first. The same is true of FOREIGN KEY, by the way (I checked), but that's a bit more obvious since breaking FOREIGN KEY will always result in a database the programmer would consider corrupt. I wrote some code in another DBMS once that betrayed that it did some really nasty things when one FOREIGN KEY triggered another one. Thanks for the help. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
> I couldn't find the answer documented anywhere, so I will have to assume that > it may change in future versions. Unless the requirement for depth first is > somewhere in the SQL specification. I believe it should be. Triggers should be executed before the statement causing them to fire is considered successful. Otherwise all triggers checking some constraints and prohibiting incorrect data (and thus raise(...) function) are useless. Pavel On Wed, Nov 25, 2009 at 8:59 AM, Simon Slavin wrote: > > On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote: > >> Does this answers question? > > I think it does for the current version: depth first. Thanks. > > I couldn't find the answer documented anywhere, so I will have to assume that > it may change in future versions. Unless the requirement for depth first is > somewhere in the SQL specification. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote: > Does this answers question? I think it does for the current version: depth first. Thanks. I couldn't find the answer documented anywhere, so I will have to assume that it may change in future versions. Unless the requirement for depth first is somewhere in the SQL specification. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive TRIGGERs: depth-first or width-first
Does this answers question?
sqlite> create table log (t);
sqlite> create table t1 (a);
sqlite> create table t2 (a);
sqlite> create trigger tt1 after update on t1 begin
...> insert into t2 values (new.a);
...> insert into log values ("update of t1, a="||new.a);
...> end;
sqlite> create trigger ttt1 after insert on t1 begin
...> insert into log values ("insert into t1, a="||new.a);
...> end;
sqlite> create trigger ttt2 after insert on t2 begin
...> insert into log values ("insert into t2, a="||new.a);
...> end;
sqlite> insert into t1 values (1);
sqlite> insert into t1 values (2);
sqlite> insert into t1 values (3);
sqlite> insert into t1 values (4);
sqlite> insert into t1 values (5);
sqlite> update t1 set a = 6;
sqlite> select rowid, t from log;
1|insert into t1, a=1
2|insert into t1, a=2
3|insert into t1, a=3
4|insert into t1, a=4
5|insert into t1, a=5
6|insert into t2, a=6
7|update of t1, a=6
8|insert into t2, a=6
9|update of t1, a=6
10|insert into t2, a=6
11|update of t1, a=6
12|insert into t2, a=6
13|update of t1, a=6
14|insert into t2, a=6
15|update of t1, a=6
Pavel
On Tue, Nov 24, 2009 at 3:20 PM, Simon Slavin wrote:
> This new implementation of recursive TRIGGERs. Is it depth-first or
> width-first ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive triggers
I've taken a further look. The main problem is that the cursor number is hardcoded, as can be seen in the following example (A delete trigger causing further deletes.) 29|OpenRead|3|6| 30|SetNumColumns|3|1| 31|Rewind|3|38| 32|Column|3|0| 33|Rowid|0|0| 34|Ne|355|37|collseq(BINARY) 35|Rowid|3|0| 36|FifoWrite|0|0| 37|Next|3|32| 38|Close|3|0| The OpenRead, SetNumColumns, Rewind, Column, Rowid, Next and Close commands in the example depend on fixed cursor identifiers. To be able to call a trigger recursively I'd need to modify the opcodes to support passing the appropriate cursor identifiers around on the stack like parameters in a C routine. My thinking was to allow negative cursor identifiers to indicate the location of the real cursor as an offset into the VDBE stack. This of course would mean, that the cursors oldIdx, newIdx need to be pushed onto the stack by the caller before the gosub and popped afterwards. In addition an opcode may be required to allocate a new cursor identifier dynamically and push it onto the stack, if the trigger requires new cursors. Do you see any possibility of supporting this without breaking other things? Mike -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. April 2007 21:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Recursive triggers "Michael Ruck" <[EMAIL PROTECTED]> wrote: > Hello, > > I've been looking into the way triggers are implemented and was > thinking about adding support for recursive triggers, as they would > simplify my current project dramatically. What was/is the reason to leave them out? > > My thoughts were adding recursive triggers by calling them like > subroutines (via the VDBE Gosub and Return) on demand. I haven't > thought this through, but wanted to ask if there are limitations or > blocking points in doing recursive triggers this way? I know that the > VDBE stack is limited and queries with very deep triggers may abort, if the stack is overflowed. > > Is the function sqlite3CodeRowTrigger, the only place I'd need to > adjust to support recursive triggers? > Recursive triggers are hard to implement correctly. But if you think you can do so by tweaking sqlite3CodeRowTrigger, then by all means give it a whirl. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recursive triggers
"Michael Ruck" <[EMAIL PROTECTED]> wrote: > Hello, > > I've been looking into the way triggers are implemented and was thinking > about adding support for recursive triggers, as they would simplify my > current project dramatically. What was/is the reason to leave them out? > > My thoughts were adding recursive triggers by calling them like subroutines > (via the VDBE Gosub and Return) on demand. I haven't thought this through, > but wanted to ask if there are limitations or blocking points in doing > recursive triggers this way? I know that the VDBE stack is limited and > queries with very deep triggers may abort, if the stack is overflowed. > > Is the function sqlite3CodeRowTrigger, the only place I'd need to adjust to > support recursive triggers? > Recursive triggers are hard to implement correctly. But if you think you can do so by tweaking sqlite3CodeRowTrigger, then by all means give it a whirl. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recursive Triggers
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
Re: [sqlite] Recursive Triggers
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/
Re: [sqlite] Recursive Triggers
On Dec 3, 2004, at 4:04 PM, D. Richard Hipp 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? Dynamic languages typically have a hard coded 'stack limit'. I think this is what Oracle does, see: http://www.oreilly.com/catalog/ordevworkbook/chapter/ch16s.html#8 Question: Can anybody suggest a way of providing support for recursive triggers which also guarantees that every SQL statement will eventually complete? Use a 'stack limit' ;) Report an error when the stack limit is reached. Unfortunately I don't know if how to implement or if this is even a feasible solution for SQLite. Best, Charlie
