Re: CREATING AUDIT TRAIL in Oracle
Date: Sun, 21 Oct 2001 10:24:26 GMT
Message-ID: <3bd29d67.92301121_at_news.webshuttle.ch>
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 - 12:24:26 CEST