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