1 2 Previous Next 16 Replies Latest reply: Nov 20, 2012 4:55 AM by 975214 RSS

    Multiple column update trigger

    724567
      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
          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
            HU
            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
              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
                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
                  HU
                  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
                    Thanks much HU,

                    I will be using the solution provided by you. That is a great help.
                    • 7. Re: Multiple column update trigger
                      724567
                      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
                        HU
                        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
                          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
                            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
                              stuck at last point... getting new and old values of updated column
                              • 12. Re: Multiple column update trigger
                                JustinCave
                                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
                                  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
                                    JustinCave
                                    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