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