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

asked 14 Jan, 08:27

Arcady%20Abramov's gravatar image

Arcady Abramov
861112
accept rate: 0%

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

(14 Jan, 08:44) Volker Barth

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.

(14 Jan, 08:53) Arcady Abramov
Replies hidden

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.

(14 Jan, 09:42) Volker Barth

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.

(14 Jan, 14:25) Breck Carter
Replies hidden

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;
(14 Jan, 14:59) Volker Barth

Thank you, Breck I shall try it

(15 Jan, 00:26) Arcady Abramov
showing 4 of 6 show all flat view

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

permanent link

answered 15 Jan, 04:51

Arcady%20Abramov's gravatar image

Arcady Abramov
861112
accept rate: 0%

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, 06:56) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×50

question asked: 14 Jan, 08:27

question was seen: 895 times

last updated: 15 Jan, 07:06