Edited by: HU on 2012-05-14 14:43
Connected to: Oracle Database 11g Release 126.96.36.199.0 - 64bit Production create table hutest (id number, c1 number, c2 number); create trigger hutesttrg after update on hutest for each row begin if updating('C1') then dbms_output.put_line('c1 updated'); end if; if updating('C2') then dbms_output.put_line('c2 updated'); end if; end; / insert into hutest values(1,1,1); update hutest set c1=2,c2=1 where id=1; c1 updated c2 updated 1 row updated. Even if c2 was updated to the same
create table hutest (id number, c1 number, c2 number); create or replace function hutestupd(inTableName in varchar2) return varchar2 is result varchar2(4000); sep varchar2(2) := null; begin for c in (select column_name from user_tab_columns where table_name = inTableName) loop if updating(c.column_name) then result := result || sep || c.column_name; sep := ', '; end if; end loop; return result; end; / create or replace trigger hutesttrg after update on hutest for each row begin dbms_output.put_line('Updated: ' || hutestupd('HUTEST')); end; / insert into hutest values(1,1,1); set serveroutput on size 100000 update hutest set c1=2 where id=1; Updated: C1 1 row updated. update hutest set c2=1 where id=1; Updated: C2 1 row updated. Tested on 188.8.131.52.0, 10.2.0.4.0 and 184.108.40.206.0
kawa alkesh wrote:Yes, you cannot dynamically reference the :new.column_name or :old.column_name values in a trigger. You can dynamically generate the trigger as was demonstrated in the other thread.
So, what I get from here is : I can't get old and new values when used dynamically in trigger itself, but I can get these values dynamically when I write a script for it. Is it so ??
Also, as per the scenario explained in my first question of this thread, shall I write a script file, store it on the machine as a file and then write a trigger to call this script ? Correct me if I am wrong... I have never done this kind of thing.. so a bit confusedI don't follow. There is no need to create a script that is stored on any machine. You can write a procedure that generates the trigger, install that procedure in your database, and call the procedure to generate the trigger whenever the structure of the table changes. Whether it really makes sense to do this, rather than just writing the trigger code manually, is something you'll have to determine.