Wouldn't you have to use two triggers, one to capture the before image, and
one to capture the after image? Ruth
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, October 17, 2002 4:38 AM
> 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ruth Gramolini
INET: rgramolini_at_tax.state.vt.us
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).
Received on Thu Oct 17 2002 - 07:48:57 CDT
