Oracle PL/SQL：动态循环触发器列

发布于 2024-07-18 03:32:08 字数 880 浏览 4 评论 0原文

在触发器内部，我尝试循环表上的所有列并将新值与旧值进行比较。这是我到目前为止所得到的：

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;

触发器编译，但是当触发器触发时，我得到：

ORA-01008: 并非所有变量都绑定

第一个绑定的所有变量都会立即执行，因为它需要 :old 的值。 :old 和 :new 已定义为触发器的一部分，但看起来立即执行无法看到这些变量。

有没有办法动态迭代触发器中的列值？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一个人的旅程 2024-07-25 03:32:08

不，您不能动态引用 :old 和 :new 值。正如 Shane 建议的那样，您可以编写代码来生成静态触发代码，如果这能让生活更轻松的话。另外，您可以将“在这里做某事”放入包过程中，以便您的触发器变为：（

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 子句）。

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).

回复收藏 0 原文