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

I have been using this system for years, adapting it from an original post I found many years ago. 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. I start with a generic audit trail table that will capture key information about all database changes:

 

CREATE TABLE AUDIT_TRAIL

( “ID” NUMBER,
“TABLE_NAME” VARCHAR2(50 BYTE),
“EVENT” VARCHAR2(10 BYTE),
“COLUMN_NAME” VARCHAR2(100 BYTE),
“OLD_VALUE” VARCHAR2(4000 BYTE),
“NEW_VALUE” VARCHAR2(4000 BYTE),
“USER_UPD” VARCHAR2(100 BYTE),
“DATE_UPD” DATE,
“PK_1″ NUMBER(10,0),
CONSTRAINT “AUDIT_TRAIL_PK” PRIMARY KEY (“ID”)
);

CREATE SEQUENCE AUDIT_TRAIL_SEQ START WITH 1 INCREMENT BY 1;
/

CREATE TRIGGER AUDIT_TRAIL_TRG
BEFORE INSERT ON audit_trail
FOR EACH ROW
BEGIN
select audit_trail_seq.nextval into :new.id from dual;
end;

/

I use a package (audit_pkg) that is called in a before update trigger (see below), that evaluates every column in the table and checks for changes. The 3 procedures are basically identical, however for number and date fields, the old and new values are converted to VARCHAR before inserting into the audit_trail table.

package audit_pkg
as
procedure check_column_var( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,
l_event in varchar2,
l_pk1 in number);

procedure check_column_num( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number,
l_event in varchar2,
l_pk1 in number);

procedure check_column_date( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,
l_event in varchar2,
l_pk1 in number);

end;
/

create or replace
package body audit_pkg
as

procedure check_column_var( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2,
l_event in varchar2,
l_pk1 in number)
is
begin

declare

v_user varchar2(100);

begin

if v(‘APP_USER’) is null then
select user into v_user from dual;
else
v_user:=v(‘APP_USER’);
end if;

if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_trail (table_name, event, column_name,
old_value, new_value,user_upd, date_upd,pk_1)
values
(upper(l_tname), l_event, upper(l_cname), l_old, l_new, v_user, sysdate,l_pk1);
end if;
end;

end check_column_var;
procedure check_column_num( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number,
l_event in varchar2,
l_pk1 in number)
is
begin

declare

v_user varchar2(100);

begin

if v(‘APP_USER’) is null then
select user into v_user from dual;
else
v_user:=v(‘APP_USER’);
end if;

if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_trail (table_name, event, column_name,
old_value, new_value,user_upd, date_upd,pk_1)
values
(upper(l_tname), l_event, upper(l_cname), to_char(l_old), to_char(l_new), v_user, sysdate,l_pk1);
end if;
end;

end check_column_num;
procedure check_column_date( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date,
l_event in varchar2,
l_pk1 in number)
is
begin

declare

v_user varchar2(100);

begin

if v(‘APP_USER’) is null then
select user into v_user from dual;
else
v_user:=v(‘APP_USER’);
end if;

if ( l_new <> l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_trail (table_name, event, column_name,
old_value, new_value,user_upd, date_upd,pk_1)
values
(upper(l_tname), l_event, upper(l_cname), to_char(l_old,’dd-mon-yyyy hh23:mi:ss’), to_char(l_new,’dd-mon-yyyy hh23:mi:ss’), v_user, sysdate,l_pk1);
end if;
end;

end check_column_date;

 

end audit_pkg;
/

I then create a trigger on every table I need to audit, firing before any update, insert or delete event.

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

On inserts, I want to capture the same information, without old value or new value.

On deletes, I want to capture the value of the primary key that was deleted.

If you run the following script in SQL*Plus, it will prompt you for your table name and spool the create trigger text for you. Run it for every table you want to audit, and there you have it! Full auditing of all data changes which you can then use in any way you choose.

Notice I refer to user_tab_columns to ensure all columns are included in the audit, and also to ensure I call the appropriate check_column procedure. I also refer to user_constraints and user_cons_columns to grab the right column value as the primary key.

 

<– begin –>

set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger &1#AUID_TRG
prompt after update or insert or delete on &1
prompt for each row
prompt begin
select ‘declare v_user varchar2(200);’ from dual;

prompt begin

select ‘ if v(”APP_USER”) is null then
select user into v_user from dual;
else
v_user:=v(”APP_USER”);
end if;’ from dual;

prompt begin

select ‘if updating then ‘ from dual;

select case when data_type=’DATE’ then ‘audit_pkg.check_column_date(”’||table_name||”’,”’||column_name||”’,:new.’||
column_name||’,:old.’||column_name||’,”UPDATE”,:new.’||(SELECT cols.column_name
FROM user_constraints cons, user_cons_columns cols
WHERE cols.table_name = ‘&1′
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner)||’);’
when data_type=’VARCHAR2′ then ‘audit_pkg.check_column_var(”’||table_name||”’,”’||column_name||”’,:new.’||
column_name||’,:old.’||column_name||’,”UPDATE”,:new.’||(SELECT cols.column_name
FROM user_constraints cons, user_cons_columns cols
WHERE cols.table_name = ‘&1′
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner)||’);’
when data_type=’NUMBER’ then ‘audit_pkg.check_column_num(”’||table_name||”’,”’||column_name||”’,:new.’||
column_name||’,:old.’||column_name||’,”UPDATE”,:new.’||(SELECT cols.column_name
FROM user_constraints cons, user_cons_columns cols
WHERE cols.table_name = ‘&1′
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner)||’); ‘

end
from user_tab_columns where table_name=’&1′;

select ‘end if;’ from dual;

select ‘if inserting then ‘ from dual;

select ‘insert into audit_trail (table_name, event, user_upd, date_upd, pk_1)
values (”&1”,”INSERT”,v_user, sysdate,:new.’||cols.column_name||’);’

from user_constraints cons, user_cons_columns cols
WHERE cols.table_name = ‘&1′
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner;
select ‘end if;’ from dual;

select ‘if deleting then ‘ from dual;
select ‘insert into audit_trail (table_name, event, user_upd, date_upd, pk_1) values
(”&1”,”DELETE”,v_user, sysdate,:old.’||cols.column_name||’); ‘
from user_constraints cons, user_cons_columns cols
WHERE cols.table_name = ‘&1′
AND cons.constraint_type = ‘P’
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner;

select ‘end if;’ from dual;

prompt end;
prompt end;
prompt end;

<–end–>

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

As an example, here is what I capture in the Audit Trail table for the following DML:

  1. Insert into LK_STATUS table (id=15)
  2. Update of 2 columns for the record I just inserted
  3. Delete of the entry
  4. Insert into LK_REGION table (id=6)
  5. Update of column
  6. Delete of the entry

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!