Forum Stats

  • 3,853,626 Users
  • 2,264,247 Discussions
  • 7,905,419 Comments

Discussions

Getting dynamic column value in trigger

Ramesh_R
Ramesh_R Member Posts: 162
edited Feb 9, 2010 12:41PM in SQL & PL/SQL
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;

Answers

  • 6363
    6363 Member Posts: 6,642
    Ramesh_R wrote:
    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.
    Can't do that.

    Simply add your trigger code to the same source repository as you table creation scripts, and when the table is updated, update and deploy the trigger too.
This discussion has been closed.