Oracle Trigger to Log column level changes of Table

The requirement was to log changes to columns on any table to a common table. The information to be recorded was Table Name, Column Name, Action (Deletion, Insertion, and Modification), Old Value, New Value, Modified User, Modified Time and Modified PC.

The solution I came up with is to create the triggers on table using a script that trigger that fires on Update Delete and Insert. This trigger sends the information to a function that checks for changes. In the function if there are changes it will be logged to the Audit table. When calling the function I checked for the ‘Action’ and according to that change the information send to it.

The functions are in a pakage and the script to create the package is below

create or replace package audit_pkg

as

    procedure check_val( l_tname in varchar2,

                             l_cname in varchar2,

                 l_new in varchar2,

                             l_old in varchar2 ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine in varchar2);

    procedure check_val( l_tname in varchar2,

                             l_cname in varchar2,

                     l_new in date,

                             l_old in date ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine in varchar2);

    procedure check_val( l_tname in varchar2,

                             l_cname in varchar2,

                 l_new in number,

                             l_old in number ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine in varchar2);

end;

/

create or replace package body audit_pkg

as

procedure check_val( l_tname in varchar2,

                     l_cname in varchar2,

             l_new in varchar2,

                     l_old in varchar2

                     ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine 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 imago_admin_audit values

        ( upper(l_tname), upper(l_cname),l_action,

                             l_old, l_new ,l_user,l_date,l_machine);

    end if;

end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,

             l_new in date, l_old in date

             ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine 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 imago_admin_audit values

        ( upper(l_tname), upper(l_cname),l_action,

          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),

          to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) ,l_user,l_date,l_machine);

    end if;

end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,

             l_new in number, l_old in number 

             ,l_action in varchar2,l_user in varchar2, l_date in varchar2,l_machine 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 imago_admin_audit values

        (upper(l_tname), upper(l_cname),l_action,

                                 l_old, l_new ,l_user,l_date,l_machine);

    end if;

end;

end audit_pkg;

/

these are three over loaded functions according by the data type of changed columns.

This is the script to create the trigger. If it is in "C:\Scripts\" , it is named as "mytrigger.sql" and the table we want to create the triger on is "Table1", then run a something this command in sql command window >@C:\Scripts\mytrigger.sql Table1

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 or delete or insert on &1

prompt for each row

prompt declare 

prompt action varchar2(255) := 'Added';;

prompt begin

prompt if updating then

prompt begin

prompt action := 'Modified';;

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||

          ''', ' || ':new.' || column_name || ', :old.' || 

             column_name || ',action , :new.modifieduser, :new.modifieddatetime, :new.modifiedmachine ' || ');'

from user_tab_columns where table_name = upper('&1') ;

prompt end;;

prompt else if deleting then

prompt begin

prompt action := 'deleted';;

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||

          ''', ' || ':new.' || column_name || ', :old.' || 

             column_name || ',action , '''', '''', '''' ' || ');'

from user_tab_columns where table_name = upper('&1') ;

prompt end;;

prompt else if inserting then

prompt begin

prompt action := 'Added';;

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||

          ''', ' || ':new.' || column_name || ', :old.' || 

             column_name || ',action , :new.createduser, :new.createddatetime, :new.createdmachine ' || ');'

from user_tab_columns where table_name = upper('&1') ;

prompt end;;

prompt end if;;

prompt end if;;

prompt end if;;

Explanation:

 If updating then

 This line checks whether this is updating so the table according to that you can set the action. Same way you  can get if it is inserting or deleting.

 ;;

 This is used to print the semicolon,otherwise it will be considered as the command, not as string to print.

 Information like 'createduser' are columns in the table that filled with the insert query and modifieduser is  filled with update query.