Why I don't need to COMMIT in database trigger?

Question

We can't COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. For example, suppose there's a database trigger:

CREATE OR REPLACE TRIGGER user_login_as
 AFTER LOGON 
 ON SCHEMA
BEGIN
 INSERT INTO user_login_log(username, log_date, action) VALUES (user, sysdate, 'User has logged in');
END user_login_as;

The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert? This triggger works like a charm and inserts new record into log table after user logon. It smells like hidden Oracle functionality and I can't find any reference in Oracle docs about that. I'm using Oracle11g.

• Aaron Bertrand: Sorry @NickChammas, meant to keep your reply intact, clicked too quickly.

Answer 1

You get an autonomous transaction context for these triggers automatically.

From the CREATE TRIGGER docs:

One or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

What happens if the trigger fails depends on the exact trigger/event. See Exception Handling in Triggers. In particular, a logon trigger that fails with an exception might very well lock non-dba users out - the exception makes the login fail unless the user has specific privileges.

• Centurion: It's interesting what happens if there occurs an exception in trigger body. I assume Oracle performs "silent" rollback in that case?
• Mat: Edited with a bit more information for that.

Answer 2

CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW
DECLARE
 PRAGMA AUTONOMOUS_TRANSACTION;
 i PLS_INTEGER; 
BEGIN
 SELECT COUNT(*)
 INTO i
 FROM t1;
 INSERT INTO t2
 VALUES
 (i);
 COMMIT;
END;
/

• András Váczi: Could you please explain what does this tell us?

Answer 3

A commit inside a trigger would defeat the basic definition of an atomic transaction (see ACID). Trigger logic is by definition an extension of the original DML operation. Changes made within triggers should thus be committed or rolled back as part of the transaction in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:

SQL> CREATE TABLE tab1 (col1 NUMBER);
Table created.
SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));
Table created.
SQL> CREATE TRIGGER tab1_trig
 2 AFTER insert ON tab1
 3 BEGIN
 4 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
 5 COMMIT;
 6 END;
 7 /
Trigger created.
SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
 *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'

Autonomous transactions:

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig
 2 AFTER insert ON tab1
 3 DECLARE
 4 PRAGMA AUTONOMOUS_TRANSACTION;
 5 BEGIN
 6 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
 7 COMMIT; -- only allowed in autonomous triggers
 8 END;
 9 /
Trigger created.
SQL> INSERT INTO tab1 VALUES (1);
1 row created.

Note that with the above example will insert and commit log entries - even if the main transaction is rolled-back!

Remember that an "autonomous_transaction" procedure/function/trigger is a whole transaction in itself and so it must end with a commit or a rollback statement.

• RDFozz: This doesn't really answer the question - the OP clearly understood why they didn't need transactional logic in DML triggers. The question was about database triggers, which the posted and accepted answer took care of.