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,
[…]