Re: CREATING AUDIT TRAIL in Oracle

On Thu, 18 Oct 2001 07:05:41 -0700, "Julian K. Black" <julianb_at_bellsouth.net> wrote:

>(...) snip (...)<

>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?
>

Use the system view DBA_TAB_COLUMNS to retrieve the field names (all uppercase). You could loop through the fields in the table by declaring a cursor which returned the column name:

SELECT column_name FROM dba_tab_columns WHERE table_name = :a_table
AND owner = :dba_user;

Check the spelling of the fields, I'm doing this off the top of my head.

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Sun Oct 21 2001 - 05:24:26 CDT