| Trigger for updating all columns of a table [message #595604] |
Thu, 12 September 2013 07:23  |
|
|
Hi,
I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.
create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);
in_new_val varchar2(100);
begin
for r in (select column_name from all_tab_columns
where table_name = 'log_dev_test'
and owner = 'LOAM')
loop
if updating(r.column_name) then
in_col_name:=r.column_name;
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
in_loamid: = :new.LOAMID;
in_lastupdateuser: = :new.lastupdateuser
if((in_old_val <> in_new_val) or (in_old_val is null and in_new_val is not null) or (in_old_val is not null and in_new_val is null)) then
EXECUTE IMMEDIATE 'INSERT INTO historylog VALUES (log_history_sequence.NEXTVAL,in_loamid, to_char(in_col_name), to_char(in_old_val),to_char(in_new_val),sysdate,in_lastupdateuser)';
exit when r%notfound;
endif;
end if;
end loop;
end;
are the below assignement of values will work ?
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name ..
Please suggest on this.
Regards,
Narendra.
|
|
|
|
| Re: Trigger for updating all columns of a table [message #595606 is a reply to message #595604] |
Thu, 12 September 2013 07:27  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
narendra.treddi wrote on Thu, 12 September 2013 17:53
are the below assignement of values will work ?
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
Just execute it in SQL*Plus. Copy paste and let us know what you get. And please use code tags to post a test case, it's difficult to understand your code.
|
|
|
|
|
|
| Re: Trigger for updating all columns of a table [message #595609 is a reply to message #595604] |
Thu, 12 September 2013 07:37 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
narendra.treddi wrote on Thu, 12 September 2013 14:23are the below assignement of values will work ?
in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;
i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name ..
Simple answer: no, it is not possible.
Complex answer: you may "dynamically" construct the code of that trigger, as demonstrated in this thread on AskTom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:734825535375
Note, that table has fixed number of columns - there is no need to change the trigger until you add/drop column (which should be rare). It would need only simple script (or whatever it will contain) call for re-creating the trigger after DDL statement changing column definition anyway.
Oracle also has inbuilt tools for achieving auditing (e.g. AUDIT statement). You should definitively explore them.
|
|
|
|
| Re: Trigger for updating all columns of a table [message #595610 is a reply to message #595604] |
Thu, 12 September 2013 07:37 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
narendra.treddi wrote on Thu, 12 September 2013 17:53
create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);
in_new_val varchar2(100);
begin
for r in (select column_name from all_tab_columns
where table_name = 'log_dev_test' and owner = 'LOAM'
That's a potential bug in the code with the way PRAGMA AUTONOMOUS_TRANSACTION is used. You are querying the table on which the trigger is defined.
A small example is here
|
|
|
|
| Re: Trigger for updating all columns of a table [message #595611 is a reply to message #595610] |
Thu, 12 September 2013 07:45 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 12 September 2013 14:37That's a potential bug in the code with the way PRAGMA AUTONOMOUS_TRANSACTION is used. You are querying the table on which the trigger is defined.
Huh? Original poster queries system view "ALL_TAB_COLUMNS", but the trigger is on "LOG_DEV_TEST". As he takes columns for "log_dev_test" (yes, case matters), I doubt it will process anything useful.
I am also curious whether auditting of columns "LOAMID" and "LASTUPDATEUSER" is intended.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
