i don't quite get how i can wrap around triggers. My stated case is i have to take that i have no access to application logic.
however, as you state below, having some kind of global access to a variable, via a function call will be very useful. net net, i see the need for simple facility like being able to say: select xid(); which could be some number or string maintained by the engine. since sqlite locks out any other write access during a transaction, this value is guaranteed to be atomic. i believe it can be implemented via a pragma, which provides a decent way in without breaking any other existing semantics. thanks brs ----- Original Message ---- From: Nicolas Williams <nicolas.willi...@oracle.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, June 23, 2010 9:39:17 AM Subject: Re: [sqlite] marking transaction boundaries 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
