Oracle PL/SQL：动态循环触发列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/786733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle PL/SQL: Loop Over Trigger Columns Dynamically
提问by Josh Bush
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
ORA-01008: 并非所有变量都绑定
on the first execute immediate because it's expecting a value for :old. :oldand :neware already defined as part of the trigger, but it appears that execute immediate can't see those variables.
在第一次立即执行，因为它期待:old. :old并且:new已经定义为触发器的一部分，但似乎立即执行看不到这些变量。
Is there a way to dynamically iterate over the column values in a trigger?
有没有办法动态迭代触发器中的列值？
回答by Tony Andrews
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:
不，您不能动态引用 :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;
(You can ditch the pointless REFERENCING clause by the way).
（顺便说一句，您可以放弃毫无意义的 REFERENCING 子句）。
回答by Dave Costa
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.
您实际上是在尝试构建自己的系统来审核对表的所有更改吗？（我对您可能对任意列的旧值和新值做什么的最佳猜测。）如果是这样，您可能需要查看 Oracle 自己的审计功能。
回答by Shane
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.
我不确定你是否能做你想做的事。您不想在 PL/SQL 代码中显式命名表列的原因是什么？如果表字段经常更改，您可以构建 PL/SQL，为每个表动态构建 PL/SQL 触发器（每个表都有明确的字段名称）。每次表更改时，您都可以运行该 PL/SQL 来生成新的触发器。
回答by devio
I had a similar problem, although in MSSQL.
我有一个类似的问题，虽然在 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.
优点是您不必在每次更新时都在元模型中移动光标，而是提前生成触发器。
