|
Hello, all I have a logging system, which works with triggers. It registers changes to fields writes them to a separate logging table. Now I have a new requirement to identify all changes, which occurred inside the same transaction. Is there a way to find out transaction id from the "after update" trigger code? Thank you Arcady |
|
Would sa_transactions( ) procedure be helpful here or is it time-consuming? Ah, thanks for the pointer - I wasn't aware of that procedure's column transaction_id! Can't tell on the performance but the following query could give the desired result (note, that this can generally return NULL but it should return a value within a trigger...): select transaction_id
from sa_transactions()
where connection_num = connection_property('Number');
Nevertheless, as these transaction_ids are volatile, I don't know whether they are useful for later diagnostics... I (wildly) guess that should at least include transaction log offsets...
(15 Jan '19, 06:56)
Volker Barth
|
Hm, I'm not aware of a way to get a "transaction ID" at all... AFAIK, there is no connection property that offers that.
Besides that, have you considered the "Audit" feature - just in case that would fit your needs...
I was hoping, since I am inside a DB trigger, which is a part of a transaction, I would be able to read it. I do not need it from the client, just inside the DB trigger.
Yes, I did get that requirement, but the mentioned properies would be available both from clients and within trigger code, so that doesn't make a difference here. I don't think there is more information available within a trigger itself, and I'm not aware of a "transaction ID" as an "official property" of a SQL Anywhere transaction.
FWIW, you can get the ending offset of the last transaction committed by the current transaction (possibly NULL) by
select connection_property('LastCommitRedoPos');but that is not a transaction ID in my understanding.
Suggestion: Create an empty dummy table with one BIGINT column that has DEFAULT AUTOINCREMENT, then every time you need to generate a new identifier, call GET_IDENTITY ( 'tablename' ).
That gives you programmatic access to a highly efficient mechanism that doesn't require any database update activity, not even locks.
I haven't used it for generating a transaction id, but I have used it to generate primary keys for multiple tables where the values are unique across all tables so that giant UNION queries can be written.
Isn't a sequence easier to use for such purposes?
Such as an one-time setup:
and after that, you can access a fresh value simply by querying (from anywhere):
Thank you, Breck I shall try it