Advertisement Space

Running independent transactions inside PL/SQL

Sometimes you need to commit or roll back work independently of the main transaction. For example, you want to log an error to a table even if the main transaction is rolled back. This is exactly what autonomous transactions are for.

Recipe #1 - Logging with autonomous transactions

The classic use case — a logging procedure that commits its own INSERT regardless of what happens to the caller's transaction:

CREATE OR REPLACE PROCEDURE log_message(
  p_message VARCHAR2,
  p_level   VARCHAR2 DEFAULT 'INFO'
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO app_log (log_date, log_level, message)
  VALUES (SYSDATE, p_level, p_message);
  COMMIT;
END;

Now you can call it from anywhere and the log entry survives even if the calling transaction fails:

BEGIN
  log_message('Starting salary update');

  UPDATE emp SET sal = sal * 1.10 WHERE deptno = 10;

  log_message('Salary update complete');
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    log_message('Error: ' || SQLERRM, 'ERROR');
    ROLLBACK; -- rolls back the UPDATE, but log entries persist
END;

Recipe #2 - Audit trigger with autonomous transaction

Triggers can't normally COMMIT because they run inside the calling transaction. An autonomous transaction lets you write audit records that persist even if the triggering statement is rolled back:

CREATE OR REPLACE TRIGGER audit_salary_change
  AFTER UPDATE OF sal ON emp
  FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO salary_audit (empno, old_sal, new_sal, changed_by, changed_at)
  VALUES (:OLD.empno, :OLD.sal, :NEW.sal, USER, SYSDATE);
  COMMIT;
END;

Important warnings