This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 20, 2012 2:55 AM by 975214 RSS

Multiple column update trigger

724567 Newbie
Currently Being Moderated
Hi,

I want to have a history track of all data that has ever been updated in a table. Say, table1 has 100 columns and to store its data change history, I have made another table track_table1 having columns : updated_column_name, old_data, new_data, mod_time.

For storing this data history, I need to write a trigger in which I will identify all columns that has value updated and insert column's name and values in the track_table1 table.

What I currently do is checking each of 100 column's new and old value inside the trigger to find out which columns have been updated. This increases the code and also oracle has to check each 100 column's values to find out whether it is being updated.

Is there any way where oracle itself can give a list of columns which have values updated by the update statement ? so that there is no need to check old and new values of every column of the table.

I am using oracle 9i and 10g databases.

-----------------------------------
Thanks much in advance,
Kawa
  • 1. Re: Multiple column update trigger
    sybrand_b Guru
    Currently Being Moderated
    if updating('<column name') then
    end if;

    Doc question!!!

    But I know no one here ever reads documentation. That requires effort!!

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: Multiple column update trigger
    714281 Explorer
    Currently Being Moderated
    But beware of what the code actually does.

    If you have code, that updates all columns, both changed and unchanged, you will need to compare old and new values!
    Connected to:
    Oracle Database 11g Release 11.2.0.2.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
    Edited by: HU on 2012-05-14 14:43
  • 3. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    Thanks much for your quick response,

    I dont want to write the IF conditions also. Is there anyway by which oracle returns the name of the columns that were updated ?
  • 4. Re: Multiple column update trigger
    sybrand_b Guru
    Currently Being Moderated
    NO

    And if so, it would have been documented.

    Depennding on version and edition, Fine Grained Audit might be able to do something.

    -----------------
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: Multiple column update trigger
    714281 Explorer
    Currently Being Moderated
    If you don't mind it listing the columns, that are updated to the same value, you can create your own function to do that:
    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 9.2.0.6.0, 10.2.0.4.0 and 11.2.0.3.0
  • 6. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    Thanks much HU,

    I will be using the solution provided by you. That is a great help.
  • 7. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    Hello HU,

    I got the column names in the trigger using the data dictionary and checking if () updated.

    Now, how can I get :old and :new values of that column inside the trigger ?
    As per the code shown by you column name is available in C.COLUMN. Now, I want old and new values of C.COLUMN.. I can't do :old.c.column_name..

    is there any way around ?
  • 8. Re: Multiple column update trigger
    714281 Explorer
    Currently Being Moderated
    The closest ting I can think of is to use select column_name from user_tab_columns ...
    to generate triggers, that compares the :old and the :new values.

    You will be back to the old way,
    but at you don't have to code them by hand.
  • 9. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    I also tried execute immediate to pass the column name concatenated as

    execute immediate 'select :old.' || C.COLUMN_NAME || ' from ......

    but :old and :new cannot be accessed dynamically.... after doing all work, I am stuck at last point, how to get the old and new values of the column being updated !!!!

    Any help greatly appreciated....
  • 10. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    Hi HU,

    since I need to store the column name whose value was updated, I need to hardcode each column for detecting its values :-(
  • 11. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    stuck at last point... getting new and old values of updated column
  • 12. Re: Multiple column update trigger
    Justin Cave Oracle ACE
    Currently Being Moderated
    That seems almost identical to a thread that was posted in this forum a couple days ago on Re: DML Trigger for schema. Basically, if you want to do this, you'll need to write code that generates the triggers and then statically refer to the :new.column_name and :old.column_name values in the trigger code you generate.

    Justin
  • 13. Re: Multiple column update trigger
    724567 Newbie
    Currently Being Moderated
    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 confused
  • 14. Re: Multiple column update trigger
    Justin Cave Oracle ACE
    Currently Being Moderated
    kawa alkesh wrote:
    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 ??
    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.
    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 confused
    I 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.

    Justin
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points