Oracle Audit table using trigger

Question

I need to create a trigger in Oracle 11g for auditing a table. I have a table with 16 columns that need to be audited.

For every new insert into a table, I need to put an entry in audit table for each column inserted i.e. in this scenario 16 row will be inserted in audit table.

For every update, suppose I update 1st and 2nd column, then it will create two record in audit with its old value and new value. Structure of audit table will be:

 id        
 mod_col_name 
 OLD VALUE 
 NEW VALUE  
 upd_time 
 mod_user_id

My approach:

create or replace trigger my_trigger
after update or insert on temp12
for each row
declare

  TYPE tab_col_nt IS table of varchar2(30);

  v_tab_col_nt tab_col_nt;

begin

    v_tab_col_nt := tab_col_nt('id','name','salary'); --in example i have given only 3 column name

    for r in v_tab_col_nt.first..v_tab_col_nt.last

    loop

        if updating(v_tab_col_nt(r)) then

            insert into audit_table values (
                id_seq.nextval, v_tab_col_nt(r), :old.v_tab_col_nt(r),
                :new.v_tab_col_nt(r), sysdate, user
             ); --here :old & :new syntex is not working
        end if;

        if inserting then
            insert into audit_table values (
               id_seq.nextval, v_tab_col_nt(r), null, 
               :new.v_tab_col_nt(r), sysdate, user);
        end if;

    end loop;
end;

My concern:

1. here :old.v_tab_col_nt(r), :new.v_tab_col_nt(r) is not working

2. how to track user id through which user is logged in GUI (dot net is front end).

I want this trigger to be written dynamically.

Answer 1

The reason why :old.v_tab_col_nt(r) and :new.v_tab_col_nt(r) are not working is because :old and :new are references ONLY for the old and new (duh!...) values in the affected columns in the table, and not for your user defined type declared inside the trigger.

The values you are actually looking for are: :old.<name of column1>, or :new.<name of column1>.

So for your requirement you trigger must look something like this:

create or replace trigger my_trigger
after insert or update on temp12
for each row
referencing old as old new as new
begin

    /*When-Insert block. 1 record for each column in audit*/
    if (INSERTING) then -- 
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
         .
         . --(same for every column)
         .
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Insert block*/

    /*When-Update block. A new record in audit just for the updated column(s) */
    if (UPDATING ( '<name of column1>' )) then --col 1
        insert into audit_table values (id_seq.nextval, '<name of column1>', :old.<name of column1>, :new.<name of column1>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column2>' )) then --col 2
        insert into audit_table values (id_seq.nextval, '<name of column2>', :old.<name of column2>, :new.<name of column2>, sysdate, user);
    end if;
    if (UPDATING ( '<name of column3>' )) then --col 3
        insert into audit_table values (id_seq.nextval, '<name of column3>', :old.<name of column3>, :new.<name of column3>, sysdate, user);
    end if;
     . 
     . --(same for every column)
     .
    if (UPDATING ( '<name of column16>' )) then --col 16
        insert into audit_table values (id_seq.nextval, '<name of column16>', :old.<name of column16>, :new.<name of column16>, sysdate, user);
    end if;
    /*end of When-Update block*/

end;

And now about tracking the logged user into your application there are two considerations:

1. If your whole application implemented all its users at a DB Level, this is a ORACLE user for each logged individual, if this is so, the reserved word "USER" within every PL/SQL block or DDL executed, will retrieve that user name every time.

2. In the other hand if your application decided to manage users on their own, by creating a custom user control, then that value must be passed either by a parameter (in case of a named PL/SQL block) or including a (NOT NULL) column in every table (at least every table you want to monitor for user activity), so that every INSERT statement is forced to send that info, and you can read it using :new.<name of user monitor column> inside your triggers code, or you can use something like

IF (:new.< name of user monitor column > is null) then raise_application_error(-20001, 'User must be specified'). END IF;

May the force be with you.