After UPDATE trigger - NEW and OLD column alias

66 Views Asked by At

I have a table with corresponding AFTER UPDATE trigger for changes auditing (Oracle 11g). Table Test01 to be audited:

CREATE TABLE Test01 
(
    Name Varchar2(40),
    Surname Varchar2(40) 
);

and table History01 for auditing of changes

CREATE TABLE History01 
(
    Old_val Varchar2(40),
    New_val Varchar2(40), 
    Changed_col Varchar2(40)
);

It works fine when I use column names with :old and :new aliases, like:

create or replace TRIGGER trg_history01
  AFTER UPDATE ON Test01
  FOR EACH ROW
BEGIN
  if :new.Name != :old.Name or ( :old.Name is NULL and :new.Name is not NULL ) then
      INSERT INTO History01 VALUES (:old.Name, :new.Name, 'Name');
  if :new.Surname != :old.Surname or ( :old.Surname is NULL and :new.Surname is not NULL ) then
      INSERT INTO History01 VALUES (:old.Surname, :new.Surname, 'Surname');
end if  
  end if;
END;

Test01 could contain more columns and so I would like to iterate over all columns instead of to use column names. But I don't know how to do it, because folowing code does not work (cannot be compiled):

create or replace TRIGGER trg_history01
  AFTER UPDATE ON Test01
  FOR EACH ROW
DECLARE
  v_old varchar2(255);
  v_new varchar2(255);
  -- col_names contains all column names from table Test01
  CURSOR col_names 
  IS 
  ( SELECT column_name FROM USER_TAB_COLUMNS 
    WHERE LOWER(table_name) = 'test01' );
BEGIN
  for col in col_names LOOP
    -- save column name into variable
    col_name := col.column_name; 
    -- to use it with OLD and NEW aliases, but followig two lines cause error "Bad bind variable"
    v_old := :OLD.col_name; 
    v_new := :NEW.col_name;
    if v_old != v_new or ( v_old is NULL and v_new is not NULL ) then
      INSERT INTO History01 VALUES ( v_old, v_new, col_name);
    end if;
  END LOOP;
END;

Is there a way how to do it or I always have to use column names?

0

There are 0 best solutions below