Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Getting dynamic column value in trigger

Ramesh RFeb 9 2010 — edited Feb 9 2010
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;

Comments

6363
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.
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 9 2010
Added on Feb 9 2010
1 comment
1,035 views