Database trigger to record user log

From: <PK_Deepa/VGIL_at_vguard.satyam.net.in>
Date: Wed, 16 Oct 2002 23:13:26 -0800
Message-ID: <F001.004EBAA6.20021016231326@fatcity.com>

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: PK_Deepa/VGIL_at_vguard.satyam.net.in

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 - 02:13:26 CDT