No inside a trigger :new.Column_name, and :old.column_name gives the before and after values
regards naveen -----Original Message----- Sent: Thursday, October 17, 2002 6:19 PM To: Multiple recipients of list ORACLE-L 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" <[EMAIL PROTECTED]> Sent: Thursday, October 17, 2002 4:38 AM > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Naveen Nahata INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
