This post was originally published on May 7th, 2012 and updated on April 17th, 2019

Does this sound familiar?

My record is gone, you need to get it back!!

When did this data change? What was it before?

I don’t know who sent this record for approval, but it wasn’t me!

Rarely do I roll out an application anymore without implementing a full DML audit trail enabling me (or a system administrator) to get a complete history of all changes that may have occurred on any record.

Whether accidental or deliberate, there WILL come a time when you need to go back and investigate a data element (or two or three..).

I have been using this system for years, adapting it from an original post I found many years ago. Sadly, I can no longer tell you where the original came from, but I thought I would share it will you as I have found it to be invaluable to me on many occasions.

The basic concept is simple. 2 simple tables to capture the auditing, a package and triggers on every table that I want to monitor.

DML Change Log Tables

CHANGE_LOG captures the table name, primary key value, event (I, U or D), user and timestamp information.

CHANGE_LOG_DATA captures more granular data about the change. In the event of an insert, it logs all column information at the time of the insert. On an update, it logs any changed columns, including both OLD and NEW values. Finally, on a delete, it logs all the old values for each column.

Change Log Table

The Package

A package (audit_pkg) that is called in a before update, insert or delete trigger (see below), and evaluates every column in the table and checks for changes. The check_val procedures are basically identical, however for number and date fields, the old and new values are converted to VARCHAR before inserting into the change log tables.

The Triggers

We then create a trigger on every table that needs to be audited, firing before any update, insert or delete events.

On updates, we want to capture the table name, column_name, primary key value, old value, new value, user and sysdate.

On inserts, we want to capture the same information, as well as the data contained in each column.

On deletes, we want to capture the contents of each column that had data in it.

Here is the trigger for the EMP table:

create or replace TRIGGER trg_aud_emp
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
DECLARE
   lv_id      CHANGE_LOG.id_change_log%TYPE;
   l_action   CHANGE_LOG.ind_action%TYPE;
   p_prc_name varchar2(50)  := 'TRG_AUD_EMP: ';
   v_user     varchar2(200) := NULL;

BEGIN
   v_user := nvl (v('APP_USER'), user);

   if inserting then
      l_action := 'I';
   end if;

   if updating then
      l_action := 'U';
   end if;

   if deleting then
      l_action := 'D';

      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'COMM',     NULL,          :OLD.comm,     lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'DEPTNO',   NULL,          :OLD.deptno,   lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'EMPNO',    NULL,          :OLD.empno,    lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'ENAME',    NULL,          :OLD.ename,    lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'HIREDATE', NULL,          :OLD.hiredate, lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'JOB',      NULL,          :OLD.job,      lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'MGR',      NULL,          :OLD.mgr,      lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:OLD.empno), 'EMP', 'SAL',      NULL,          :OLD.sal,      lv_id, l_action);
   else
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'COMM',     :NEW.comm,     :OLD.comm,     lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'DEPTNO',   :NEW.deptno,   :OLD.deptno,   lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'EMPNO',    :NEW.empno,    :OLD.empno,    lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'ENAME',    :NEW.ename,    :OLD.ename,    lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'HIREDATE', :NEW.hiredate, :OLD.hiredate, lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'JOB',      :NEW.job,      :OLD.job,      lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'MGR',      :NEW.mgr,      :OLD.mgr,      lv_id, l_action);
      AUDIT_PKG.check_val (to_char (:NEW.empno), 'EMP', 'SAL',      :NEW.sal,      :OLD.sal,      lv_id, l_action);
   end if;

EXCEPTION
   when others then
      raise_application_error (-20000, 'Error ' || p_prc_name || to_char (sqlcode) || ' - ' || sqlerrm);
END trg_aud_emp;

DML Change Log Create Trigger Function

The package also contains a function that accepts a table name and automatically generates the appropriate trigger for it. This makes implementation super easy!

It queries user_tab_columns to ensure all columns are included in the audit, and also to ensure it call the appropriate check_column procedure. It also refers to user_constraints and user_cons_columns to grab the right column value as the primary key.

FUNCTION fu_generate_trigger_b (p_table_name USER_TABLES.table_name%TYPE,
p_msg_return IN OUT varchar2)
RETURN boolean;

Very simple to create, and you could even go a step a further and query user_tables and create the triggers for all your tables in a single go!

As an example, here is what I capture in the Change Log Data table for the following DML:

  1. Update of EMP table (EMPNO: 7698, SAL updated from 2850 to 3000 (yellow)
  2. Insert of a new EMP record (pink)
  3. Delete of an EMP record (green)

The only caveat is that this table will grow quickly, so plan accordingly! Also, when doing: mass updates/inserts/initial database loads, it may be worthwhile disabling the triggers so as not to unnecessarily populate the audit trail table.

It’s a simple yet powerful auditing system that ensures all changes are traceable!

I cannot tell you how many times I have been able to answer a user’s questions and even recover data quite simply.