I've researched firing of these triggers somewhat. Upshot: firing anything other than a transaction commit trigger is tricky.
The semantics of transaction triggers are interesting. Presumably such triggers should execute in the same transaction as the one they are related to -- but this would mean that rollback triggers can't have any side-effects in the database, which begs the question as to what their utility might be. Upshot: I'm going to ignore rollback triggers for now. And what happens if a rollback occurs after or while running commit triggers? IMO: forget about rollback triggers, and if a commit trigger's work is undone in a rollback, that's fine. The semantics of database connect triggers should be simple, but they aren't: what if the trigger can't execute because of SQLITE_BUSY? Tricky. Loop? On the other hand, if these triggers only execute when the first write transaction on that db handle begins, then that issue goes away. That seems much better. The semantics of database disconnect triggers is also tricky, mostly because of how sqlite3_close() works (it can fail with SQLITE_BUSY part-way through). Scenario 1: a disconnect trigger fires but the db connection stays alive, in which case we may have to re-fire the connect trigger again later. Scenario 2: part of the connection may be torn down when the disconnect triggers fire (e.g., virtual tables, which may mean having to re-initialize their state, which means that vtabs could see spurious connects and disconnects). Transaction start triggers are tricky because the act of firing them causes a write transaction to be started, so we can't fire them in a "BEGIN DEFERRED", but then we need to defer firing the trigger, which means we need to track whether it has fired, and this needs to be done such that we can decide whether to code the transaction start trigger while parsing. Transaction rollback triggers are even trickier because they can be completely async. Coding one one of these such that it gets executed when the transaction is rolled back and not otherwise is tricky (and then one has to worry about the trigger itself doing a rollback). I'd be tempted to re-enter the VM with sqlite3_prepare/step/finalize in order to fire this trigger, but I worry that that may break various assumptions. And then there's the question: should a rollback trigger fire inside the transaction that's rolling back? If so the only side effects that such a trigger could have would be to invoke user-defined functions with external side-effects. If not... Ah, maybe I could code these trigger firings as alternatives to OP_Halt, terminated by OP_Halt, and with their own OP_Transaction so they can execute in their own transaction. Upshot: I'm going to focus on transaction commit for now, then transaction start, and I may stop there. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
