Re: [sqlite] Advice on breaking trigger recursion?
On Fri, Jan 07, 2011 at 09:54:07PM -0600, Nicolas Williams wrote: > On Sat, Jan 08, 2011 at 01:29:23AM +, Simon Slavin wrote: > > On 8 Jan 2011, at 1:12am, Nicolas Williams wrote: > > > I need to use recursive triggers. In some cases I want to "normalize" > > > values of some columns of NEW being INSERTed or UPDATEd, but there's no > > > UPDATE syntax for changing NEW, thus I can't write something like: > > > > > > CREATE TRIGGER fred INSTEAD OF INSERT ON myTable ... > > Oh, for some reason I thought that INSTEAD OF triggers were for views > only, but that's not the case. [...] Actually, I was unable to get SQLite3 to create INSTEAD OF triggers on tables. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On Sat, Jan 08, 2011 at 12:07:08PM +0700, Dan Kennedy wrote: > On 01/08/2011 08:12 AM, Nicolas Williams wrote: > > I need to use recursive triggers. In some cases I want to "normalize" > > values of some columns of NEW being INSERTed or UPDATEd, but there's no > > UPDATE syntax for changing NEW, thus I can't write something like: > > > > [...] > > > > I must write: > > > > CREATE TRIGGER ... > > BEGIN > > UPDATE SET somecol = () > > WHERE rowid = NEW.rowid; > > END; > > Something like this perhaps: > > CREATE TRIGGER sometrig AFTER UPDATE WHEN somecol != BEGIN >UPDATE sometab SET somecol = WHERE rowid = NEW.rowid; > END; Yes, adding a WHEN clause to the triggers also works. I did have some triggers with WHEN clauses, actually, but it didn't occur to me to use that as a technique for breaking recursion in part, I think, because the WHEN expression would, in this case, be several lines long, and aesthetically that rubbed me the wrong way. I've got several instances of that sort of expression in my code and it occurs to me that I can use additional views to reduce the length of that expression. Thanks to Dan as well. CHeers, Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On 8 Jan 2011, at 6:38am, Nicolas Williams wrote: > Thanks to Drake Wilson and Simon Slavin. The trick was to create a VIEW > that the application uses and which has INSTEAD OF triggers to do the > right thing. This breaks the recursive triggering because there are no > recursive triggers left on the underlying table. Worked like a charm. No problem. I had forgotten the problem with recursion. I'm glad Drake mentioned it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
Thanks to Drake Wilson and Simon Slavin. The trick was to create a VIEW that the application uses and which has INSTEAD OF triggers to do the right thing. This breaks the recursive triggering because there are no recursive triggers left on the underlying table. Worked like a charm. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On 01/08/2011 08:12 AM, Nicolas Williams wrote: > I need to use recursive triggers. In some cases I want to "normalize" > values of some columns of NEW being INSERTed or UPDATEd, but there's no > UPDATE syntax for changing NEW, thus I can't write something like: > > CREATE TRIGGER ... > BEGIN > UPDATE SET NEW.somecol = (); > END; > > I must write: > > CREATE TRIGGER ... > BEGIN > UPDATE SET somecol = () > WHERE rowid = NEW.rowid; > END; Something like this perhaps: CREATE TRIGGER sometrig AFTER UPDATE WHEN somecol != BEGIN UPDATE sometab SET somecol = WHERE rowid = NEW.rowid; END; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On Sat, Jan 08, 2011 at 01:29:23AM +, Simon Slavin wrote: > On 8 Jan 2011, at 1:12am, Nicolas Williams wrote: > > I need to use recursive triggers. In some cases I want to "normalize" > > values of some columns of NEW being INSERTed or UPDATEd, but there's no > > UPDATE syntax for changing NEW, thus I can't write something like: > > > CREATE TRIGGER fred INSTEAD OF INSERT ON myTable ... Oh, for some reason I thought that INSTEAD OF triggers were for views only, but that's not the case. Yes, I see now that to change NEW I can just use INSTEAD OF triggers: CREATE TRIGGER foo_trig INSTEAD OF INSERT ON foo BEGIN INSERT INTO bar (some_col) SELECT ; END; Handy. Thanks! The insert in the trigger body surely has to be on a different table than the trigger is for, else the instead-of trigger will recurse, right? So I still need a table or view for the application to use as a stand-in for the actual table. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On Fri, Jan 07, 2011 at 06:29:05PM -0700, Drake Wilson wrote: > Quoth Nicolas Williams , on 2011-01-07 19:12:13 > -0600: > > But the real problem is that my triggers will just recurse infinitely, > > since I need both, AFTER INSERT and AFTER UPDATE triggers. The AFTER > > INSERT trigger will trigger the AFTER UPDATE trigger, and that one will > > trigger itself, recursing ad naseum. This [obviously] happens in the > > case of UPDATEs too. > > > > I need a way to break this recursion. > > > > One idea I'm considering is to have two columns where I have one: > [...] > > Another idea is that I could use temp triggers and temp tables instead, > [...] > > Pardon me if this is too awkward, but could you not use a trivial view > (AS SELECT * FROM ...) and then make INSTEAD OF INSERT/UPDATE/DELETE > triggers on that? The application could use the view exclusively; the > physical table would be in the background, and the view would act like > a shadow table. (The DELETE case would just be transparent and key on > the row IDs, I expect, if you don't need to do anything there.) Hmmm, yes, that seems very likely to work. I'll give it a whirl. Thanks! Any other ideas? I would like to be able to add triggers to an existing schema without having to change everything around nor disable recursive triggers... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
On 8 Jan 2011, at 1:12am, Nicolas Williams wrote: > I need to use recursive triggers. In some cases I want to "normalize" > values of some columns of NEW being INSERTed or UPDATEd, but there's no > UPDATE syntax for changing NEW, thus I can't write something like: CREATE TRIGGER fred INSTEAD OF INSERT ON myTable ... See http://www.sqlite.org/lang_createtrigger.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Advice on breaking trigger recursion?
Quoth Nicolas Williams , on 2011-01-07 19:12:13 -0600: > But the real problem is that my triggers will just recurse infinitely, > since I need both, AFTER INSERT and AFTER UPDATE triggers. The AFTER > INSERT trigger will trigger the AFTER UPDATE trigger, and that one will > trigger itself, recursing ad naseum. This [obviously] happens in the > case of UPDATEs too. > > I need a way to break this recursion. > > One idea I'm considering is to have two columns where I have one: [...] > Another idea is that I could use temp triggers and temp tables instead, [...] Pardon me if this is too awkward, but could you not use a trivial view (AS SELECT * FROM ...) and then make INSTEAD OF INSERT/UPDATE/DELETE triggers on that? The application could use the view exclusively; the physical table would be in the background, and the view would act like a shadow table. (The DELETE case would just be transparent and key on the row IDs, I expect, if you don't need to do anything there.) ---> Drake Wilson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Advice on breaking trigger recursion?
I need to use recursive triggers. In some cases I want to "normalize" values of some columns of NEW being INSERTed or UPDATEd, but there's no UPDATE syntax for changing NEW, thus I can't write something like: CREATE TRIGGER ... BEGIN UPDATE SET NEW.somecol = (); END; I must write: CREATE TRIGGER ... BEGIN UPDATE SET somecol = () WHERE rowid = NEW.rowid; END; And such triggers have to be AFTER triggers too, since the row to update wouldn't exist in the BEFORE INSERT case... But the real problem is that my triggers will just recurse infinitely, since I need both, AFTER INSERT and AFTER UPDATE triggers. The AFTER INSERT trigger will trigger the AFTER UPDATE trigger, and that one will trigger itself, recursing ad naseum. This [obviously] happens in the case of UPDATEs too. I need a way to break this recursion. One idea I'm considering is to have two columns where I have one: one that the application sets, and one that the triggers set, with the application selecting the latter in its queries. This will work, but it seems too complicated (the app sets one column but WHERE clauses must use the other, and SELECTs must fetch the other as well), and will waste space on disk (I probably don't care about that). Another idea is that I could use temp triggers and temp tables instead, and have the app do inserts into the temp tables and them have triggers that make suitable changes to the actual DB (INSERT OR REPLACE) and not triggers on the actual DB. This too will work, but now the app has to do INSERTs exclusively, and on a temp table, and it has to execute statements to create the temp triggers at DB open time. Or I might make that additional table not a temp table and just delete all rows from it before COMMITing (it'd be nice to have transaction-level triggers to automate such cleanup). Either way though the need to break infinite trigger recursion affects the "interface" seen by the application in obnoxious ways. Maybe I've missed a better way to break trigger recursion that wouldn't have this problem? What might that be? I tried using pragma to toggle trigger recursivity inside the trigger bodies, but this, unsurprisingly, didn't work. A brief search does not turn up generic trigger recursion techniques that don't rely on DBMS-specific extensions. Even if I missed some technique, it might be useful to be able to declare that a given INSERT/UPDATE/DELETE statement in a trigger must not recurse. It would also be nice to have UPDATE syntax for updating the row in question (NEW), which would not recurse. Also, it'd be convenient to have a sticky recursive_triggers pragma, as otherwise one has to execute a pragma statement on every DB open. Any help would be much appreciated. Thanks, and happy New Year, Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
