Hi all,
I have a secenario like to check the old values and new values of a record inside a trigger
like :new.col_name = :old.col_name. The scenario is i need to dynamically iterate thro' all
the column values. because the number of columns is large and keeps on changing in future too.
sample:
CREATE OR REPLACE TRIGGER AUDIT_TRIGGER BEFORE
UPDATE ON EMPLOYEE_TABLE REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare
oldval varchar(2000);
newval varchar(2000);
begin
for row in (SELECT column_name from user_tab_columns where table_name='EMPLOYEE_TABLE' ) loop
execute immediate 'select :old.'||row.column_name||' from dual' into oldval;
execute immediate 'select :new.'||row.column_name||' from dual' into newval;
--Do something here with the old and new values
end loop;
end;