Saturday, July 20, 2013

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.




6 comments:

  1. There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

    Oracle Course in Chennai | Oracle Training in Chennai | hadoop training in chennai

    ReplyDelete
  2. Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    SAS Training in Chennai | SAS Course in Chennai

    ReplyDelete
  3. Much obliged to you for requiring significant investment to give us a portion of the valuable and restrictive data with us.
    Regards,
    SAS Training Institute in Chennai | SAS Training
    Chennai
    | SAS Courses in Chennai

    ReplyDelete
  4. Thanks admin, I had spent more time with your website, because this is one of the best resources I have found it. Your information is really useful to me.
    Regards,
    oracle Training in Chennai | oracle Training center in Chennai | oracle courses in Chennai

    ReplyDelete

  5. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  6. You start to squeeze the trigger... and it jerks. There goes your aim, the deer you were targeting runs off into the trees, frightened but unscathed. How do you control your trigger? BRIAN

    ReplyDelete