On Wed, Jun 23, 2010 at 09:19:05AM -0700, b s scratched on the wall:
> I can put the transaction identifier into tables via triggers.
>
> That's the million $ question. I don't know when transaction
> begins or ends! And my original question was pointing to
> a feature DRH had suggested way back in 2003. That would
> have been the perfect way to do it (and probably the only
> reliable way).
>
> It would have been something along these lines:
> CREATE TRIGGER ex1 AFTER BEGIN BEGIN
> INSERT or UPDATE into TT values ('xid', lower(hex(randomblob(16)))
> END;
>
> CREATE TRIGGER ex2 BEFORE COMMIT BEGIN
> DELETE from TT where key='xid'
> END;
>
> CREATE TRIGGER abc_ins AFTER INSERT on abc
> UPDATE abc set xid = (select value from TT where key='xid')
> END;
If something like this existed, the update to the TT table will
never be seen outside of this transaction. You've basically setup
localized per-transaction variable.
Why not just do it in the application? Just create a transaction id
in the application anytime you call BEGIN and manually use the id
with any INSERT statement.
Let me guess... you want to do all this, but you can't modify the
application, only the database. I don't think that is possible.
> for this to work, we need a database intrinsic. please refer to
> this link again:
> http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html
I think the end result of this work was the sqlite3_*_hook() APIs.
The functionality was exposed at the C API level, rather than the SQL
level. As the message points out, there doesn't seem to be any
other database engine that provides this kind of functionality
at the SQL level.
Still, you could likely use commit hooks to do this. Create a
commit hook that generates a fresh transaction id and stores it in
an application variable. Create an SQL function that returns that ID.
Create a trigger like your third one, where you call the SQL function
to get the value. Every time a transaction commits, it prepares a
fresh ID for the next transaction. You would need per-connection
values, but if your application is only using one connection at a
time, it is pretty simple.
You could even do all that in an external extension. You'd need to
somehow load the extension, but you wouldn't need any other
modifications to the application.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
