Entity Change Logging

I recently had a requirement to track changes to fields with certain records. What I needed to track was the id of the record that changed, the field that changed, the old and new values and who and when it was changed. Credit must go to the "Ask Tom" forum for the basic idea. My modification to thie original idea was to track changes at the record level rather than the table level. So I am able to access changes to a specific record with any given entity.

Here's a link to the original inspiration: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

In Oracle, a trigger gives you acces to two variable ":old" and ":new"; :new and :old are like bind variables to the trigger, they are not 'regular' variables.

you cannot dynamically access them, only 'statically'.

The challenge then is to write a trigger that statically references the new/old values.

First of all we need a table to track changes:

 
CREATE TABLE ENTITY_CHANGE_LOG
(
  CHANGE_ID               VARCHAR2(50 BYTE)     NOT NULL,
  ENTITY_RECORD_ID        VARCHAR2(50 BYTE)     NOT NULL,
  ENTITY_FIELD            VARCHAR2(32 BYTE)     NOT NULL,
  OLD_VALUE               VARCHAR2(100 BYTE),
  NEW_VALUE               VARCHAR2(100 BYTE),
  CHANGED_BY              VARCHAR2(50 BYTE),
  CHANGE_DATE             DATE
);
 

The define a package:

 
​create or replace package entity_change_api
as
    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in varchar2,
                         l_old in varchar2 );

    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in date,
                         l_old in date );

    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in number,
                         l_old in number );
end;              

and it's body:

 
create or replace package body entity_change_api
as

    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in varchar2,
                         l_old in varchar2 )
is
begin
    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 ENTITY_CHANGE_LOG (
   CHANGE_ID, ENTITY_RECORD_ID, ENTITY_FIELD,
   OLD_VALUE, NEW_VALUE, CHANGED_BY,
   CHANGED_DATE)
  VALUES ( sys_guid(),
   l_entity_rec_id,
   upper(l_cname),
   l_old,
   l_new,
   user,
   sysdate );            
    end if;
end;

    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in date,
                         l_old in date )
is
begin
    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 ENTITY_CHANGE_LOG (
   CHANGE_ID, ENTITY_RECORD_ID, ENTITY_FIELD,
   OLD_VALUE, NEW_VALUE, CHANGED_BY,
   CHANGED_DATE)
  VALUES ( sys_guid(),
   l_entity_rec_id,
   upper(l_cname),
   to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
   to_char( l_new, 'dd-mon-yyyy hh24:mi:ss' ),
   user,
   sysdate );    
    end if;
end;

    procedure check_val( l_entity_rec_id in varchar2,
                         l_cname in varchar2,
                         l_new in number,
                         l_old in number )
is
begin
    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 ENTITY_CHANGE_LOG (
   CHANGE_ID, ENTITY_RECORD_ID, ENTITY_FIELD,
   OLD_VALUE, NEW_VALUE, CHANGED_BY,
   CHANGED_DATE)
  VALUES ( sys_guid(),
   l_entity_rec_id,
   upper(l_cname),
   l_old,
   l_new,
   user,
   sysdate ); 
    end if;
end;

end entity_change_api;

Then generate the trigger body with this script

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

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select '    entity_change_api.check_val( ' || ':old.ID' || ', ''' || column_name ||
          ''', ' || ':new.' || column_name || ', :old.' ||
             column_name || ');'
from user_tab_columns where table_name = upper('&1')

which will create a trigger statement like the following:

 
create or replace trigger aud#audit_documents
after update on audit_documents
for each row
begin 
    entity_change_api.check_val( :old.ID, 'AUDIT_ID', :new.AUDIT_ID, :old.AUDIT_ID);
    entity_change_api.check_val( :old.ID, 'CUSTOMER_ID', :new.CUSTOMER_ID, :old.CUSTOMER_ID);
    entity_change_api.check_val( :old.ID, 'AUTH_CODE', :new.AUTH_CODE, :old.AUTH_CODE);
    entity_change_api.check_val( :old.ID, 'REPOSITORY_ID', :new.REPOSITORY_ID, :old.REPOSITORY_ID);
    entity_change_api.check_val( :old.ID, 'VALIDATION_STATUS', :new.VALIDATION_STATUS, :old.VALIDATION_STATUS);
    entity_change_api.check_val( :old.ID, 'CREATED_BY', :new.CREATED_BY, :old.CREATED_BY);
    entity_change_api.check_val( :old.ID, 'CREATED_DATE', :new.CREATED_DATE, :old.CREATED_DATE);
    entity_change_api.check_val( :old.ID, 'UPDATED_BY', :new.UPDATED_BY, :old.UPDATED_BY);
    entity_change_api.check_val( :old.ID, 'UPDATED_DATE', :new.UPDATED_DATE, :old.UPDATED_DATE);
end;
 

this will log changes to a specific "entity_record_id":