Live ORACLE

DROP TABLE emp_audit CASCADE CONSTRAINTS PURGE;


CREATE TABLE emp_audit (
 emp_audit_id NUMBER(6),
 up_date      DATE,
 new_sal      NUMBER(8,2),
 old_sal      NUMBER(8,2));


-- create an autonomous trigger that inserts into the audit table before

-- each update of salary in the employees table

CREATE OR REPLACE TRIGGER audit_sal

 BEFORE UPDATE OF salary ON employees

 FOR EACH ROW

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp_audit

 VALUES     (:old.employee_id,

             SYSDATE,

             :new.salary,

             :old.salary);

 COMMIT;

END;

/


-- update the salary of an employee, and then commit the insert

UPDATE employees
SET    salary = salary
                 * 1.05
WHERE  employee_id = 115;


COMMIT;


-- update another salary, then roll back the update

UPDATE employees
SET    salary = salary
                 * 1.05
WHERE  employee_id = 116;


ROLLBACK;


-- show that both committed and rolled-back updates add rows to audit table

SELECT *
FROM   emp_audit
WHERE  emp_audit_id = 115
       OR emp_audit_id = 116;

Using Autonomous Triggers