| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Database trigger to record user log
Could you please explain with an example .
Thanks in advance
Deepa
|--------+------------------------------------->
| | Chaim.Katz_at_Completions.Bomb| | | ardier.com | | | | | | 17/10/2002 08:14 PM | | | Please respond to ORACLE-L | | | |
|--------+------------------------------------->
>--------------------------------------------------------| | | | To: Multiple recipients of list ORACLE-L | | <ORACLE-L_at_fatcity.com> | | cc: (bcc: PK Deepa/VGIL) | | Subject: Re: Database trigger to record user | | log | >--------------------------------------------------------|
PK_Deepa/VGIL_at_vguard.satyam.net.in_at_fatcity.com on 10/17/2002 03:13:26 AM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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..
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
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Chaim.Katz_at_Completions.Bombardier.com 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). -- 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 Sat Oct 19 2002 - 04:38:36 CDT
 |
 |