Identifying transaction ID in a trigger

[former_SQLA_member1694896]

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

[VolkerBarth]

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...

[former_SQLA_member1694896]

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.

[VolkerBarth]

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.

[Breck_Carter]

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.

[VolkerBarth]

Isn't a sequence easier to use for such purposes?

Such as an one-time setup:

create sequence TransIdGenerator cycle;

and after that, you can access a fresh value simply by querying (from anywhere):

select TransIdGenerator.nextval;

[former_SQLA_member1694896]

Thank you, Breck I shall try it

[former_SQLA_member1694896]

Would sa_transactions( ) procedure be helpful here or is it time-consuming?

[VolkerBarth]

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...