CREATING AUDIT TRAIL in Oracle

Hi gang

 I am trying to create an audit trail for my database application. My audit trail table as the following
structure: t_audit_trail

             {user_ID  INTEGER
              date  DATE
              type_of_change  VARCHAR2(10)
    field_name VARCHAR2(30)
              table_name VARCHAR2(30)
              old_value  VARCHAR2(30)
              new_value  VARCHAR2(30) }

I am creating a before insert and update trigger row level trigger. Capturing the user_id, date, type_of_change, table_name pose no problem. The problem is with the field_name, old_value, and new_value. Some of my tables have
30 fields and I would hate to have to list the name of every field in my code. Does anyone have a suggestion other than refering to every field in my code? If so can you please provide a working example of it?

I was thinking about using the system tables to capture the field names for each table and looping thru each value and trying to capture the old and the new value. If so can you please provide a working example of it?

thanks in advance. Received on Thu Oct 18 2001 - 16:05:41 CEST