auditing 50 columns using oracle trigger

Question

I need to create a trigger in oracle 11g for auditing a table .

I have a table with 50 columns that need to be audited.

• For every new insert into a table ,i need to put an entry in audit table (1 row).
• For every update ,suppose i update 1st 2nd column ,then it will create two record in audit with its old value and new value .

structure of audit table will be

 id        NOT NULL
 attribute NOT NULL
 OLD VALUE NOT NULL
 NEW VALUE NOT NULL
 cre_date  NOT NULL
 upd_date  NULL
 cre_time  NOT NULL
 upd_time  NULL

In case of insert ,only the primary key (main table)i.e the id and cre_date and cre_time need to be populated and attribute equal to * ,in case of update ,suppose colA and colB is updating then all need to be populated.In this case two records will be created with attribute of first record colA and corresponding old and new value , and same for the colB

Now my solution to audit is not very optimized , i have created a row level trigger ,which will check for each and every 50 columns for that table whether it is been changed or not based on its new and old value(if -else) , and it will populate the audit table . I am not satisfied with my soltuion thats why i am posting here. Another solution which i have seen in the link below :

http://stackoverflow.com/questions/1421645/oracle-excluding-updates-of-one-column-for-firing-a-trigger

This is not working in my case , I have done a POC for that as shown below:

create table temp12(id number);

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');

   for r in v_tab_col_nt.first..v_tab_col_nt.last
   loop
      if updating(r) then
         insert into data_table values(1,'i am updating'||r);
      else
      insert into data_table values(2,'i am inserting'||r);
      end if;
   end loop;

 end;

In case of updating it is calling the else part i don't know why . Can this be possible through compound trigger

Answer 1

Your immediate problem with the else always being called is because you're using your index variable r directly, rather than looking up the relevant 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 data_table values(1,'i am updating '||v_tab_col_nt(r));
    else
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end if;
end loop;

You're also only showing an id column in your table creation, so when r is 2, it will always say it's inserting name, never updating. More importantly, if you did have a name column and were only updating that for a given id, this code would show the id as inserting when it hadn't changed. You need to split the insert/update into separate blocks:

if updating then
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        if updating(v_tab_col_nt(r)) then
            insert into data_table values(1,'i am updating '||v_tab_col_nt(r));
        end if;
    end loop;
else /* inserting */
    for r in v_tab_col_nt.first..v_tab_col_nt.last loop
        insert into data_table values(2,'i am inserting '||v_tab_col_nt(r));
    end loop;
end if;

This will still say it's inserting name even if the column doesn't exist, but I assume that's a mistake, and I guess you'd be trying to populate the list of names from user_tab_columns anyway if you really want to try to make it dynamic.

---

I agree with (at least some of) the others that you'd probably be better off with an audit table that takes a copy of the whole row, rather than individual columns. Your objection seems to be the complication of individually listing which columns changed. You could still get this information, with a bit of work, by unpivoting the audit table when you need column-by-column data. For example:

create table temp12(id number, col1 number, col2 number, col3 number);
create table temp12_audit(id number, col1 number, col2 number, col3 number,
    action char(1), when timestamp);

create or replace trigger temp12_trig
before update or insert on temp12
for each row
declare
    l_action char(1);
begin
    if inserting then
        l_action := 'I';
    else
        l_action := 'U';
    end if;

    insert into temp12_audit(id, col1, col2, col3, action, when)
    values (:new.id, :new.col1, :new.col2, :new.col3, l_action, systimestamp);
end;
/

insert into temp12(id, col1, col2, col3) values (123, 1, 2, 3);
insert into temp12(id, col1, col2, col3) values (456, 4, 5, 6);
update temp12 set col1 = 9, col2 = 8 where id = 123;
update temp12 set col1 = 7, col3 = 9 where id = 456;
update temp12 set col3 = 7 where id = 123;

select * from temp12_audit order by when;

        ID       COL1       COL2       COL3 A WHEN
---------- ---------- ---------- ---------- - -------------------------
       123          1          2          3 I 29/06/2012 15:07:47.349
       456          4          5          6 I 29/06/2012 15:07:47.357
       123          9          8          3 U 29/06/2012 15:07:47.366
       456          7          5          9 U 29/06/2012 15:07:47.369
       123          9          8          7 U 29/06/2012 15:07:47.371

So you have one audit row for each action taken, two inserts and three updates. But you want to see separate data for each column that changed.

select distinct id, when,
    case
        when action = 'I' then 'Record inserted'
        when prev_value is null and value is not null
            then col || ' set to ' || value
        when prev_value is not null and value is null
            then col || ' set to null'
        else col || ' changed from ' || prev_value || ' to ' || value
    end as change
from (
    select *
    from (
        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)
order by when, id;

        ID WHEN                      CHANGE
---------- ------------------------- -------------------------
       123 29/06/2012 15:07:47.349   Record inserted
       456 29/06/2012 15:07:47.357   Record inserted
       123 29/06/2012 15:07:47.366   col1 changed from 1 to 9
       123 29/06/2012 15:07:47.366   col2 changed from 2 to 8
       456 29/06/2012 15:07:47.369   col1 changed from 4 to 7
       456 29/06/2012 15:07:47.369   col3 changed from 6 to 9
       123 29/06/2012 15:07:47.371   col3 changed from 3 to 7

The five audit records have turned into seven updates; the three update statements show the five columns modified. If you'll be using this a lot, you might consider making that into a view.

So lets break that down just a little bit. The core is this inner select, which uses lag() to get the previous value of the row, from the previous audit record for that id:

        select id,
            col1, lag(col1) over (partition by id order by when) as prev_col1,
            col2, lag(col2) over (partition by id order by when) as prev_col2,
            col3, lag(col3) over (partition by id order by when) as prev_col3,
            action, when
        from temp12_audit

That gives us a temporary view which has all the audit tables columns plus the lag column which is then used for the unpivot() operation, which you can use as you've tagged the question as 11g:

    select *
    from (
        ...
    )
    unpivot ((value, prev_value) for col in (
        (col1, prev_col1) as 'col1',
        (col2, prev_col2) as 'col2',
        (col3, prev_col3) as 'col3')
    )

Now we have a temporary view which has id, action, when, col, value, prev_value columns; in this case as I only have three columns, that has three times the number of rows in the audit table. Finally the outer select filters that view to only include the rows where the value has changed, i.e. where value != prev_value (allowing for nulls).

select
    ...
from (
    ...
)
where value != prev_value
    or (value is null and prev_value is not null)
    or (value is not null and prev_value is null)

I'm using case to just print something, but of course you can do whatever you want with the data. The distinct is needed because the insert entries in the audit table are also converted to three rows in the unpivoted view, and I'm showing the same text for all three from my first case clause.