Create an Audit trigger 1

[CassidyHunt]

I need a generic trigger that I can place on any table that will write to an audit table the following:

table_name
column_name
user_id
old_value
new_value
change_date

I am picturing a loop to loop through the columns of the table and any column that doesn't match the old value be recorded in this table. I am not quite sure on the syntax.

Hopefully I explained this right.

thanks for the help in advance.

Cassidy

 

[Dagon]

It would depend on exactly what your requirements but, from the fact that you are proposing a loop, I would suggest you haven't entirely understood how triggers work. There is no need for a loop because the trigger automatically fires for each row that has been updated. In fact, a loop would actually be impossible because the table is in a state of being changed and you would get a mutating table error.

As an example of how you would proceed, I've produced the following:

SQL> create table audex (a number primary key, b varchar2(20), c varchar2(20));

Table created.

SQL> 
SQL> create table audittrail (a number,
  2                           old_b varchar2(20), new_b varchar2(20),
  3                           old_c varchar2(20), new_c varchar2(20));

Table created.

SQL> 
SQL> insert into audex values (1, 'X', 'X');

1 row created.

SQL> 
SQL> create or replace trigger audext before update on audex for each row
  2  begin
  3     if nvl(:new.b, '$$') <> nvl(:old.b, '$$') or nvl(:new.c, '$$') <> nvl(:old.c, '$$') then
  4        insert into audittrail (a, old_b, new_b, old_c, new_c)
  5        values (:old.a, :old.b, :new.b, :old.c, :new.c);
  6     end if;
  7  end;
  8  /

Trigger created.

SQL> 
SQL> update audex set b = 'Z';

1 row updated.

SQL> 
SQL> select * from audittrail;

         A OLD_B                NEW_B                OLD_C                NEW_C
---------- -------------------- -------------------- -------------------- --------------------
         1 X                    Z                    X                    X

Retired (not by choice) Oracle contractor.

 

[CassidyHunt]

Is there a way to generically handle the columns?

ie

for i in table.columns
loop
 if new:i <> old:i then
   insert into audittrail(tablename,columnname, username, old_value, new_value,changedate) values("table.name",i,"username",old:i,new:i,sysdate);
 end if;
end loop;

I know that isn't right but hopefully it illustrates what I am trying. Basically I can then apply this on any table I want to audit without having to rewrite the column loop.

Thanks for hte help

Cassidy

 

[Dagon]

I don't think there is a way to do it completely dynamically (unless there's some new functionality in 11g). However, you could use SQL to generate most of the trigger, so you don't have to write it all out.

For example, all the if statements at the body could be generated using:

select
'if :new.'||column_name||' <> :old.'||column_name||' then '||chr(10)||
'  insert into audittrail(tablename,columnname, username, old_value, new_value,changedate)'||chr(10)||
'  values('''||table_name||''','''||column_name||''',user,'||chr(10)||
'         old:'||column_name||',new:'||column_name||',sysdate);'||chr(10)||
'end if;'
from user_tab_columns
where table_name = 'TAB_TO_BE_AUDITED'
/

Be careful about NULLS however. If one side of the comparison is NULL and the other side has a value, the whole expression equates to NULL rather than TRUE, so it doesn't execute the code inside the IF statement. That's why my example used an NVL technique to handle this.



Retired (not by choice) Oracle contractor.

 

[Thargy]

Also, be advised that Oracle has built-in functions for providing audit, i.e. you turn on what you need, you write (and therefore maintain) no code of your own.

Is there any particular reason for not using oracle's built-in tools?

Regards

T

 

[CassidyHunt]

I haven't used them before and only needed to do one table. Figure I would make it expandable.

Anyplace to study up on that functionality?

 

[Thargy]

Yes,

the oracle on-line documentation gives chapter and verse. Also yer man Thomas Kyte Esquire has quite a lot on his web site too. There are numerous options available, but don't even write one trigger.

Nothing is more expandable that a built-in function, because, by its very nature, it already is capable of auditing every table in the db (should you want that).

Regards

T