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
