Oracle PL/SQL - Autonomous Trigger Logs INSERT Statements

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL SQL Statement
  4. Declaring Autonomous Function in Package

Introduction

In the following code, whenever a row is inserted into the emp table, a trigger inserts the same row into a log table.

Because the trigger is autonomous, it can commit changes to the log table regardless of whether they are committed to the main table.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w  w . j ava2s  . co m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DROP TABLE emp;

Table dropped.

SQL> CREATE TABLE emp AS SELECT * FROM emp;
SQL>
SQL> -- Log table:
SQL>
SQL> DROP TABLE log;

Table dropped.

SQL> CREATE TABLE log (
  2    log_id   NUMBER(6),
  3    up_date  DATE,
  4    new_sal  NUMBER(8,2),
  5    old_sal  NUMBER(8,2)
  6  );
SQL>
SQL> -- Autonomous trigger on emp table:
SQL>
SQL> CREATE OR REPLACE TRIGGER log_sal
  2    BEFORE UPDATE OF salary ON emp FOR EACH ROW
  3  DECLARE
  4    PRAGMA AUTONOMOUS_TRANSACTION;
  5  BEGIN
  6    INSERT INTO log (
  7      log_id,
  8      up_date,
  9      new_sal,
 10      old_sal
 11    )
 12    VALUES (
 13      :old.empid,
 14      SYSDATE,
 15      :new.salary,
 16      :old.salary
 17    );
 18    COMMIT;
 19  END;
 20  /

SQL> UPDATE emp
  2  SET salary = salary * 1.05
  3  WHERE empid = 115;

0 rows updated.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> UPDATE emp
  2  SET salary = salary * 1.05
  3  WHERE empid = 116;

0 rows updated.

SQL>
SQL> ROLLBACK;

Rollback complete.

SQL>
SQL> -- Show that both committed and rolled-back updates
SQL> -- add rows to log table
SQL>
SQL> SELECT * FROM log
  2  WHERE log_id = 115 OR log_id = 116;

no rows selected

SQL>

Related Topic