Re: How to get current transaction ID

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 22 Dec 1998 13:53:25 GMT
Message-ID: <367fa320.1778587@192.86.155.100>

A copy of this was sent to "John Smith" <johnsmith120_at_hotmail.com> (if that email address didn't require changing) On Mon, 21 Dec 1998 22:02:33 -0800, you wrote:

>Hi,
>
>I prepare triggers on a few tables to monitor changes. I need to know that
>some of them were done in one and the same transaction. I think that the
>best way to get it is to write current transaction id to my audit tables.
>Any idea how to get current transaction id in database trigger?
>
>Thanks in advance,
>John
>johnsmith120_at_hotmail.com
>

You can get this from v$transaction, for example:

SQL> create table emp as select * from scott.emp; Table created.

SQL>
SQL> create or replace package my_state   2 as
  3 tid varchar2(255);
  4 end;
  5 /
Package created.

SQL> create or replace trigger my_trigger   2 after update on emp for each row
  3 declare
  4 l_tid varchar2(255);
  5 begin

  6          select XIDUSN || '.' || XIDSLOT || '.' || XIDSQN into l_tid
  7            from v$transaction
  8           where addr = ( select taddr from v$session where audsid
                             =userenv('sessionid') );
  9  
  9          if ( l_tid != my_state.tid or my_state.tid is NULL )
 10          then
 11                dbms_output.put_line('New Transaction has been entered...');
 12                dbms_output.put_line( 'Tid = ' || l_tid );
 13                my_state.tid := l_tid;
 14          else
 15                dbms_output.put_line( 'Same old transaction...' );
 16          end if;

 17 end;
 18 /
Trigger created.

SQL> update emp set ename = lower(ename) where rownum = 1; New Transaction has been entered...
Tid = 5.5.563

1 row updated.

SQL> update emp set ename = upper(ename) where rownum = 1; Same old transaction...

1 row updated.

SQL> update emp set ename = lower(ename) where rownum = 1; Same old transaction...

1 row updated.

SQL> commit;

Commit complete.

SQL> update emp set ename = lower(ename) where rownum = 1; New Transaction has been entered...
Tid = 2.24.721

1 row updated.

SQL> update emp set ename = upper(ename) where rownum = 1; Same old transaction...

1 row updated.

SQL> update emp set ename = lower(ename) where rownum = 1; Same old transaction...

1 row updated.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Dec 22 1998 - 07:53:25 CST