Re: [sqlite] playing with triggers
On 19 Aug 2010, at 11:22pm, Igor Tandetnik wrote: > No, cascading triggers (one trigger causing another to fire) have "always" > worked ("always" meaning long enough that I can't recall when this was *not* > the case). Recursive triggers (a trigger causing itself to fire, directly or > indirectly) are relatively new (a couple years old) and have to be explicitly > enabled, for backward compatibilty with existing schemas. Thanks. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with triggers
Simon Slavin wrote: > If that worries you then you should be aware that the same problem applies > when one TRIGGER triggers another: > > http://www.sqlite.org/pragma.html#pragma_recursive_triggers > > You have to remember to turn it on in your application. My understanding of > the term 'recursive triggers' is that it refers not > just to when a trigger triggers itself (what 'recursive' means to me), but > also when any trigger triggers another. However I may > be wrong about this. No, cascading triggers (one trigger causing another to fire) have "always" worked ("always" meaning long enough that I can't recall when this was *not* the case). Recursive triggers (a trigger causing itself to fire, directly or indirectly) are relatively new (a couple years old) and have to be explicitly enabled, for backward compatibilty with existing schemas. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with triggers
On 19 Aug 2010, at 9:00pm, David Bicking wrote: > I haven't tried RAISE(ROLLBACK... as that seems to severe. > RAISE(ABORT... removes the initial insert to Table1, which I want to avoid. > RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave > the first three there, which I can't let happen. It is all or nothing for > data changes below Table1. > > Which leads me to believe I can't do what I want without application code > supervising the changes. With requirements that complicated you either need to work each TRIGGER differently or, as you say, implement the logic in your application. >> with special regard to those two constructions and see >> >> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > > I do have foreign keys declared. Mind you in my tests they don't work if I > forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it > seems like I really can't trust foreign keys and it is best to never enable > them. If that worries you then you should be aware that the same problem applies when one TRIGGER triggers another: http://www.sqlite.org/pragma.html#pragma_recursive_triggers You have to remember to turn it on in your application. My understanding of the term 'recursive triggers' is that it refers not just to when a trigger triggers itself (what 'recursive' means to me), but also when any trigger triggers another. However I may be wrong about this. > Or is there a way to force them to be enabled at all times? (i.e. not trust > me to remember to have any and all applications that talk to the data file to > remember to issue the pragma statement.) http://www.sqlite.org/pragma.html#pragma_foreign_keys says in part "As of SQLite version 3.6.19, the default setting for foreign key enforcement is OFF. However, that might change in a future release of SQLite. To minimize future problems, applications should set the foreign key enforcement flag as required by the application and not depend on the default setting." So the answer is not yet, but maybe in a future release. However, there's a similar note about triggers ! Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with triggers
On Aug 19, 2010, at 4:00 PM, David Bicking wrote: > I haven't tried RAISE(ROLLBACK... as that seems to severe. > RAISE(ABORT... removes the initial insert to Table1, which I want to avoid. > RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave > the first three there, which I can't let happen. It is all or nothing for > data changes below Table1. > > Which leads me to believe I can't do what I want without application code > supervising the changes. Would SAVEPOINTs help you here? http://www.sqlite.org/lang_savepoint.html I've never tried using ROLLBACK TO in a trigger. e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with triggers
--- On Thu, 8/19/10, Simon Slavin wrote: > > On 19 Aug 2010, at 8:10pm, David Bicking wrote: > > > The way it is set up, if any of the updates/inserts > done by the triggers fail, everything rolls back, including > the original data that caused the triggers. What I want to > happen is that while everything else gets rolled back, > Table1 still has its data, along with the error messages > returned by the triggers. > > > > Nearest I can tell you can't do that with triggers, > but I really don't understand how they work, so maybe I am > wrong. > > You may be able to do this with ON CONFLICT and > RAISE. See > > http://www.sqlite.org/lang_createtrigger.html > I did read that, and think I mostly understand it, With it, and help from this list, I have gotten as far as I have. I haven't tried RAISE(ROLLBACK... as that seems to severe. RAISE(ABORT... removes the initial insert to Table1, which I want to avoid. RAISE(FAIL.. on lets say the fourth record inserted in to Table2, would leave the first three there, which I can't let happen. It is all or nothing for data changes below Table1. Which leads me to believe I can't do what I want without application code supervising the changes. > with special regard to those two constructions and see > > http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers > I do have foreign keys declared. Mind you in my tests they don't work if I forget to issue Pragma foreign_keys=on. Since I am a forgetful person, it seems like I really can't trust foreign keys and it is best to never enable them. Or is there a way to force them to be enabled at all times? (i.e. not trust me to remember to have any and all applications that talk to the data file to remember to issue the pragma statement.) Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] playing with triggers
On 19 Aug 2010, at 8:10pm, David Bicking wrote: > The way it is set up, if any of the updates/inserts done by the triggers > fail, everything rolls back, including the original data that caused the > triggers. What I want to happen is that while everything else gets rolled > back, Table1 still has its data, along with the error messages returned by > the triggers. > > Nearest I can tell you can't do that with triggers, but I really don't > understand how they work, so maybe I am wrong. You may be able to do this with ON CONFLICT and RAISE. See http://www.sqlite.org/lang_createtrigger.html with special regard to those two constructions and see http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers for some examples. However, a lot of work that's done with triggers is really about foreign keys, and SQLite now implements foreign keys so you don't have to simulate them in such a bulky way. Read http://www.hwaci.com/sw/sqlite/foreignkeys.html before you get too into triggers. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users