Oracle Database Audit Trigger
This is a trigger (after insert/update/delete) to audit track data changes in a table where data changes frequently and to store all values as they are changed. The trigger can also track values, usernames etc. if necessary (with related table and PL/SQL code enhancement).

Data Audit Trigger
-- After Insert/Update/Delete Trigger to 
-- populate mfg_operation_id, initial and
-- current mfg_status for auditing purposes
-- (can be enhanced to include username etc.)

CREATE OR REPLACE TRIGGER mfg_sch_status_aiud_trig
 AFTER INSERT OR UPDATE OR DELETE ON 
 Mfg_Schedule_Status
 FOR EACH ROW
BEGIN
 IF INSERTING THEN
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id, initial_mfg_status, created_date)
   SELECT
    :NEW.mfg_operation_id, :NEW.mfg_status, SYSDATE
   FROM DUAL;
 ELSIF UPDATING THEN
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id,
     initial_mfg_status, current_mfg_status, updated_date)
   SELECT
    :NEW.mfg_operation_id,
    :OLD.mfg_status, :NEW.mfg_status, SYSDATE
   FROM DUAL;
 ELSIF DELETING
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id, current_mfg_status, updated_date)
   SELECT
    :OLD.mfg_operation_id, :OLD.mfg_status, SYSDATE 
   FROM DUAL;
 END IF;

END mfg_sch_status_aiud_trig;
/
 
Data Audit Table
DESC Mfg_Schedule_Status_Audit
Name                          Null?      Type
----------------------------- ---------- ---------------
mfg_operation_id              NOT NULL   NUMBER
initial_mfg_status                       VARCHAR2(30)
current_mfg_status                       VARCHAR2(30)
created_date                             DATE
updated_date                             DATE

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 23, 2012

  1400