LLI Wiki — My Digital Knowledge Base

Oracle column-level audit trigger

Oracle trigger template that logs the old and new value of each column, on INSERT and on UPDATE.

This Oracle trigger template writes the old and new value of each column to an audit table. On an INSERT, every column produces an audit row. On an UPDATE, only the columns actually changed are logged.

How it works

Trigger

Replace my_table, audit_table and the column names with those of your schema. The table audit_table and the sequence id_seq must exist.

CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT OR UPDATE ON my_table
FOR EACH ROW
REFERENCING OLD AS old NEW AS new
BEGIN

    /* INSERT: one audit row per column */
    IF (INSERTING) THEN
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column1>', :old.<name of column1>, :new.<name of column1>, SYSDATE, USER);
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column2>', :old.<name of column2>, :new.<name of column2>, SYSDATE, USER);
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column3>', :old.<name of column3>, :new.<name of column3>, SYSDATE, USER);
        -- ... (same for every column)
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column16>', :old.<name of column16>, :new.<name of column16>, SYSDATE, USER);
    END IF;

    /* UPDATE: one audit row only for the updated columns */
    IF (UPDATING ('<name of column1>')) THEN
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column1>', :old.<name of column1>, :new.<name of column1>, SYSDATE, USER);
    END IF;
    IF (UPDATING ('<name of column2>')) THEN
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column2>', :old.<name of column2>, :new.<name of column2>, SYSDATE, USER);
    END IF;
    IF (UPDATING ('<name of column3>')) THEN
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column3>', :old.<name of column3>, :new.<name of column3>, SYSDATE, USER);
    END IF;
    -- ... (same for every column)
    IF (UPDATING ('<name of column16>')) THEN
        INSERT INTO audit_table VALUES (id_seq.NEXTVAL, '<name of column16>', :old.<name of column16>, :new.<name of column16>, SYSDATE, USER);
    END IF;

END;
/

Notes

Mastodon LLIMastodon SocialFosstodon