Geek asks and answers

Oracle PL / SQL: cyclical trigger columns dynamically

Inside the trigger, I try to iterate over all the columns of the table and compare the new values ​​with the old values. Here is what I still have:

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 get:

ORA-01008: not all variables are related

on first execution immediately, because it expects a value for :old. :oldand :newalready defined as part of the trigger, but it seems that the immediate start of these variables cannot be specified.

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

+3
source share
4 answers

, : old : new values ​​. , , . , " - " , :

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;

(, REFERENCING).

+5

, , . PL/SQL? , PL/SQL, PL/SQL ( ). , , PL/SQL .

+4

? ( , .) , Oracle.

+4

, MSSQL.

, , ( , ), . .

, , .

+2

More articles:


All Articles