Oracle PL / SQL:动态循环触发器列 [英] Oracle PL/SQL: Loop Over Trigger Columns Dynamically
问题描述
在触发器内部,我试图循环一个表上的所有列,并将新值与旧值进行比较。这是我到目前为止:
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:并非所有变量绑定
ORA-01008: not all variables bound
执行立即,因为它期待一个值:old
。 :旧的
和:新的
已经被定义为触发器的一部分,但似乎执行立即无法看到这些变量。
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?
推荐答案
不,您不能动态地引用:old和:新值。正如Shane所说,您可以编写代码来生成静态触发器代码,如果这使得生活更轻松。此外,您可以将做某事放入程序包中,以使您的触发器变为:
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;
(您可以通过这种方式来剔除无意义的REFERENCING子句)。
(You can ditch the pointless REFERENCING clause by the way).
这篇关于Oracle PL / SQL:动态循环触发器列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!