Could you please explain with an example . Thanks in advance Deepa
|--------+-------------------------------------> | | [EMAIL PROTECTED]| | | ardier.com | | | | | | 17/10/2002 08:14 PM | | | Please respond to ORACLE-L | | | | |--------+-------------------------------------> >--------------------------------------------------------| | | | To: Multiple recipients of list ORACLE-L | | <[EMAIL PROTECTED]> | | cc: (bcc: PK Deepa/VGIL) | | Subject: Re: Database trigger to record user | | log | >--------------------------------------------------------| [EMAIL PROTECTED]@fatcity.com on 10/17/2002 03:13:26 AM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: I guess, the problem you're having is that, ':new'||v_column is understood as a string and not as a variable? I have used a standard trigger (actually there was more than one) for a similar requirement (a couple of years ago). We needed a transaction log for about many tables, the table structures were changing (in a controlled way) but frequently... We ignored the :new and :old variables. Instead we read the row again based on tablename (and rowid or something similar) by a generated SQL statement using all_tab_columns; done in a way that that avoided the mutating table error. This effectively gets the :new values. We also read the the row again in another session (connected to the first by dbms_pipes) (but today maybe we could use an autonomous transaction) to get the :old values. Then we compared the the two rows, column by column and wrote the differences to the log. It took a bit of time to design and code, but was maintenance free. Table layouts could be changed, new tables could be added, views could be included, all without worrying about the trx logging portion. BTH don't forget a unique identifier to the original row in your update log. Good luck, Chaim 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 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: 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: 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).