Oracle PL/SQL: Loop Over Trigger Columns Dynamically

Question

Inside of a trigger I'm trying to loop over all columns on a table and compare the new values to the old values. Here is what I have so far:

CREATE OR REPLACE TRIGGER "JOSH".TEST#UPD BEFORE 
UPDATE ON "JOSH"."TEST_TRIGGER_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='TEST_TRIGGER_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;

The trigger compiles, but when the trigger fires, I'm getting:

ORA-01008: not all variables bound

on the first execute immediate because it's expecting a value for :old. :old and :new are already defined as part of the trigger, but it appears that execute immediate can't see those variables.

Is there a way to dynamically iterate over the column values in a trigger?

Solution 1

No, you cannot reference :old and :new values dynamically. As Shane suggests, you can write code to generate the static trigger code, if that makes life easier. Also, you can make "do something here" into a package procedure so that your trigger becomes:

CREATE OR REPLACE TRIGGER JOSH.TEST#UPD BEFORE 
UPDATE ON JOSH.TEST_TRIGGER_TABLE
begin    
   my_package.do_something_with (:old.col1, :new.col1);
   my_package.do_something_with (:old.col2, :new.col2);
   my_package.do_something_with (:old.col3, :new.col3);
   -- etc.
end;

(You can ditch the pointless REFERENCING clause by the way).

Solution 2

Are you essentially trying to build your own system to audit all changes to the table? (My best guess as to what you might be doing with the old and new values of arbitrary columns.) If so, you might want to look into Oracle's own auditing capabilities.

Solution 3

I'm not sure if you can do what you are trying to do. What is the reason you don't want to explicitly name the table columns inside the PL/SQL code? If the table fields are changing often, you could build PL/SQL that dynamically builds the PL/SQL trigger for each table (with the explicit field names in each). Each time the table changes, you could run that PL/SQL to generate the new trigger.

Solution 4

I had a similar problem, although in MSSQL.

My solution was to write a stored procedure which iterates through tables and columns information (either via dictionary views or a custom repository) and generates the required triggers. The procedure needs to be run only if the data model changes.

The advantage is that you don't have to cursor through the metamodel in each update, but rather generate your triggers in advance.

View more solutions

Comments

[Josh Bush]

I was kind of afraid that this might be the case. I was just trying to keep the code as simple as possible and avoid a big mess of the same code over and over.