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?