Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: commit for triggers

RE: commit for triggers

From: John Flack <>
Date: Fri, 23 Jan 2004 20:44:26 -0800
Message-ID: <>

Mark - Thanks for the correction. When I looked at what I said about the transaction before a DDL command a second time, I myself wondered if I'd gotten it right. If you've tested it, and the transaction is always committed, I'll take your word for it.

Arup - I don't normally use Oracle's built-in auditing of DML, I write my own audits with triggers, and it works as I said. If you've tested this, I'll take your word for it. That said, if it DOES work the way you say, I personally think it works the wrong way. If I update a table, and then roll back the update, I don't want an audit table record of the update, unless it CLEARLY notes the fact that the update was rolled back. I'm much more interested in the fact that Jack changed the table, than in the fact that Manny started to change it, but then changed his mind.

-----Original Message-----
Sent: Friday, January 23, 2004 11:09 PM
To: Multiple recipients of list ORACLE-L

Perhaps I got it wrong, but, John - are you saying that the entries are part of the rollback, i.e. if the transaction that caused the audit trail entries to be created is rolled back, the audit trail enries are rolled back as well?

The auditing entry is NOT part of the transaction, it's created via an autonomous one and it stays in the audit trail table, regardless of what happens to the transaction.

It will take a very simple test to prove this.

Make sure that audit_trail is set to DB.

create table atest1 (col1 number, col2 number, col3 number, col4 number);

insert into atest1 values (1,1,1,1);

audit update on atest1 by access;

update atest1 set col1 = 2;

Do NOT commit.

>From another session as user SYS,

select action_name, obj_name, ses_actions, returncode from dba_audit_trail;

ACTION_NAME                 OBJ_NAME             SES_ACTIONS
--------------------------- -------------------- ------------------- -------
UPDATE                      ATEST1


The entry is there even if the transaction is not committed.

Now rollback the update and check the audit trail; it will be there.

If the auditing option were BY SESSION, instead of action, the ACTION_NAME would have been SESSION REC and the column SES_ACTIONS would've been "----------S-----".

Hope this helps.


> John,
> I agree w/ everything you said, except for the autocommit functionality.
> Autocommit setting has no impact on whether DDL will commit or rollback
> any in progress transaction. DDL always commits an in-progress
> transaction. The short example below speaks for itself. ( on
> Solaris 2.8)
> SQL> show autocommit
> autocommit OFF
> SQL> desc a
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> SQL> select * from a where col1=-12345;
> no rows selected
> SQL> insert into a values(-12345,-12345);
> 1 row created.
> SQL> create table xxx(a number);
> Table created.
> SQL> select * from a where col1=-12345;
> ---------- ----------
> -12345 -12345
> 1 row selected.
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Imagination was given to man to compensate him for what he is not, and
> a sense of humor was provided to console him for what he is." --Unknown

Please see the official ORACLE-L FAQ:
Author: John Flack

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 23 2004 - 22:44:26 CST

Original text of this message