How to get updated columns indices on BEFORE UPDATE trigger on Oracle?

Question

In my application users can update some datas. And I want to save all updated columns with their values in AG_TABLE_UPDATE_HISTORY table. The table's structure is like that:

CREATE TABLE "AG_TABLE_UPDATE_HISTORY"
  (
    "TABLE_NAME"     VARCHAR2(20 BYTE),
    "ROW_ID"         VARCHAR2(20 BYTE),
    "COLUMN_NAME"    VARCHAR2(20 BYTE),
    "PREVIOUS_VALUE" VARCHAR2(20 BYTE),
    "CURRENT_VALUE"  VARCHAR2(20 BYTE)
  )

I know that, I can get updated column's names by comparing :old and :new columns one by one in BEFORE UPDATE trigger. But, I want to know is there any better way which is offered to us by Oracle?

And http://stackoverflow.com/questions/2337325/can-i-copy-old-and-new-pseudo-records-in-to-an-oracle-stored-procedure — Rob van Laarhoven, Apr 14 '14 at 14:07
possible duplicate of [Oracle - Triggers to create a history row on update](http://stackoverflow.com/questions/2238683/oracle-triggers-to-create-a-history-row-on-update) — Bob Jarvis - Слава Україні, Apr 14 '14 at 15:44

score 1 · Accepted Answer · answered Apr 14 '14 at 13:48

The answer depends on you want to keep just values what were changed in UPDATE statement or you want to track the fact what columns have been updated. Oracle suggests UPDATING() function which works in triggers, maybe this can help:

SQL> create table t (x int, y varchar2(10), z date)
  2  /

SQL> insert into t values(1,'A',sysdate)
  2  /

SQL> create or replace trigger tr_t
  2  before update on t
  3  for each row
  4  begin
  5    if updating('X') then
  6      dbms_output.put_line('Old X is '||:old.X);
  7      dbms_output.put_line('New X is '||:new.X);
  8    end if;
  9    if updating('Y') then
 10      dbms_output.put_line('Old Y is '||:old.Y);
 11      dbms_output.put_line('New Y is '||:new.Y);
 12    end if;
 13    if updating('Z') then
 14      dbms_output.put_line('Old Z is '||:old.Z);
 15      dbms_output.put_line('New Z is '||:new.Z);
 16    end if;
 17  end;
 18  /

SQL> set serveroutput on
SQL> update t set x = 2;
Old X is 1                                                                      
New X is 2                                                                      

1 row updated.

SQL> update t set x = 1, y='C';
Old X is 2                                                                      
New X is 1                                                                      
Old Y is A                                                                      
New Y is C                                                                      

1 row updated.

SQL> update t set x = 1, y='C', z = sysdate+1;
Old X is 1                                                                      
New X is 1                                                                      
Old Y is C                                                                      
New Y is C                                                                      
Old Z is 14.04.14                                                               
New Z is 15.04.14                                                               

1 row updated.

Thanks, but I have already said that:I know that, I can get updated column's names by comparing `:old` and `:new` columns one by one in `BEFORE UPDATE` trigger. — Farhad Jabiyev, Apr 14 '14 at 13:53
UPDATING() function says what the column was a part of UPDATE statement even if old and new values where the same - see the last update statement. This is the difference between UPDATING() and values comparing. — Dmitry Nikiforov, Apr 14 '14 at 13:55
Thanks. Though this not the best way, this way is more appropriate for my situation. — Farhad Jabiyev, Apr 14 '14 at 16:09

How to get updated columns indices on BEFORE UPDATE trigger on Oracle?

1 Answers1