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).