How to create a trigger to record changes for Insert,Update,Delete for multiple tables.

Question

I have two tables in my Database.This can increase later on.I want to add another table Audit to track changes to the existing two tables.I want to track any change done to any of these table AUdit Table structure is
ID
Table_Name
Field_Name
Old_Value
New_Value
Modified_By
Date_of_Modification

SO now I want to have one trigger for both tables which can be fired on an insert,update or delete to either of my tables. When this trigger is fired I want to insert values in Audit.When I'm updating I want the old value and new value.When inserting I want old value as nothing and new value as Inserted.When Deleting I want old value as the old existing value and new value as deleted.

Answer 1

I'm not quite sure what your question is. The triggers can use the :OLD and :NEW keywords like this:

create trigger table1_trg
after insert or update or delete on table1
for each row
begin
   if :old.col1 is null and :new.col1 is not null
   or :old.col1 is not null and :new.col1 is null
   or :old.col1 != :new.col1 
   then
      insert into audit_table ...
   end if;

   -- Ditto for col2, col3, ...
end;

There is no generic way to do this, you will have to have code for each column. However, you can encapsulate the logic like this:

procedure log_col_change
   ( p_table_name varchar2
   , p_column_name varchar2
   , p_old_val varchar2
   , p_new_val varchar2
   )
is
begin
   if p_old_val is null and p_new_val is not null
   or p_old_val is not null and p_new_val is null
   or p_old_val != p_new_val 
   then
      insert into audit_table ...
   end if;
end;

-- Overloaded version to handles DATE columns without losing time component
procedure log_col_change
   ( p_table_name varchar2
   , p_column_name varchar2
   , p_old_val date
   , p_new_val date
   )
is
begin
   log_col_change (p_table_name, p_column_name
                  , to_char(p_old_val,'YYYY-MM-DD HH24:MI:SS')
                  , to_char(p_new_val,'YYYY-MM-DD HH24:MI:SS')
                  );
end;

The trigger is then:

create trigger table1_trg
after insert or update or delete on table1
for each row
begin
   log_col_change ('MYTABLE', 'COL1', :old.col1, :new.col1);
   log_col_change ('MYTABLE', 'COL2', :old.col2, :new.col2);
   ... etc.
end;

NB Best practice would be to put the procedures into a package.