On Tue, Jun 22, 2010 at 08:35:14AM -0700, b s wrote: > hi, > long ago, drh had proposed a trigger like mechanism that > can be invoked at the begin/end of a transaction. > http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html > > the general consensus was there is no use other than up'ng > a counter. however, i have a requirement where i would like > to mark transaction boundaries from a user level.
That's not how I read that thread. Around-transaction triggers are incredibly useful (they're even more useful with a procedural language or at least some way to apply logic to all rows modified/inserted/ deleted by the transaction). Since there aren't any, your only choice is to manually wrap the transaction statements -- at least the COMMIT statement, while the BEGIN could be handled with a temp table and a trigger to do an INSERT OR IGNORE into it which you DELETE on COMMIT. CREATE TEMP TABLE current_transaction (rowid INTEGER PRIMARY KEY, tran_id TEXT); -- The trigger has to be a TEMP trigger too... CREATE TEMP TRIGGER ... INSERT OR IGNORE INTO current_transaction VALUES (1, randomblob(16)); UPDATE ... SET NEW.tran_id = (SELECT tran_id FROM current_transaction); END; ... and add "DELETE FROM current_transaction;" before every COMMIT. (You also need to do this DELETE on rollbacks. If you re-open your DB connection for every transaction (well, OK, that's heavy-duty), then you don't even need to wrap the COMMIT, but you do need to create the temp table before you BEGIN. So that's your choice: wrap the BEGIN or the COMMIT/ROLLBACK, or both. Back to the 2003 thread... Triggers on ROLLBACK, BEFORE BEGIN or AFTER COMMIT obviously can't modify the DB, so they are only useful for invoking user-defined functions, really, but that _is_ a useful feature! Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users