Re: Database trigger to record user log

PK_Deepa/VGIL_at_vguard.satyam.net.in wrote:
>
> Hi ,
>
> We want to create a database trigger to maintain the log history of
> transaction tables (Not the Oracle Archive Log).
>
> Our requirement is to create a common Oracle database trigger. Only the
> table name will be different in these
> triggers. The column names can be taken from "all_tab_columns" view.
>
> When a row is updated in the table, this trigger should fire and the old
> and new values of updated fields must be
> saved in the update_log table. It's structure would be :
>
> create table update_log
> (
> log_date date,
> table_name varchar2(20),
> column_name varchar2(20),
> old_value varchar2(20),
> new_value varchar2(20)
> );
>
> We have tried out this trigger, but in vain..
> -----------------------------------------------------------
> CREATE OR REPLACE TRIGGER trg_invoice
> after update on sales.invoice
> referencing old as old new as new
> for each row
>
> declare
> cursor cur_log
> is
> select column_name from all_tab_columns
> where table_name='USER_PROG_ROLES';
>
> v_column varchar2(50);
>
> begin
>
> for rec_log in cur_log loop
> v_column := rec_log.column_name;
> if ':new.'||v_column <> ':old.'||v_column then -- -- how to get old
> and new value without
> -- -- knowing the actual column name.
> insert into update_log
> (log_date, table_name, column_name, old_value, new_value)
> values
> (sysdate, 'Sales.invoice', v_column, :old.v_column, :new.v_column);
> end if;
> end loop;
>
> end;
> -----------------------------------------------------------
>
> Expecting a reply soon...
>
> Thanks in advance,
>
> Deepa
>

Deepa,

   Change the requirement. I think that the best you can do is a generator for as many triggers as you have tables you want to monitor. The best I know to do in terms of generic triggers is catching statement and bind variables, and even this involves having a peek at places where you are not supposed to. Or use LogMiner, of which I know very little.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).