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.

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.

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;
/

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.