oracle PL/SQL 触发器 - 动态引用 :NEW 或 :OLD
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/687882/
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
PL/SQL Trigger - Dynamically reference :NEW or :OLD
提问by Matthew Watson
Is it possible to dynamically reference the :NEW/OLD pseudo records, or copy them?
是否可以动态引用 :NEW/OLD 伪记录，或复制它们？
I'm doing a audit trigger for a very wide table, so would like to avoid having separate triggers for insert/delete/update.
我正在为一个非常宽的表做一个审计触发器，所以想避免为插入/删除/更新设置单独的触发器。
When updating/inserting I want to record the :NEW values in the audit table, when deleting I want to record the :OLD values.
更新/插入时我想在审计表中记录 :NEW 值，删除时我想记录 :OLD 值。
回答by venu
create or replace trigger audit_tgrbefore insert or update or delete on 'table_name'
audit_tgr在“table_name”上插入或更新或删除之前创建或替换触发器
for each row
begin
if (inserting or updating) then
insert into audit table (a,b,c) values(:new.a,:new.b,:new.c);
else
insert into audit table (a,b,c) values(:old.a,:old.b,:old.c);
end;
回答by Tony Andrews
You could try:
你可以试试：
declare
l_deleting_ind varchar2(1) := case when DELETING then 'Y' end;
begin
insert into audit_table (col1, col2)
values
( CASE WHEN l_deleting_ind = 'Y' THEN :OLD.col1 ELSE :NEW.col1 END
, CASE WHEN l_deleting_ind = 'Y' THEN :OLD.col2 ELSE :NEW.col2 END
);
end;
I found that the variable was required - you can't access DELETING directly in the insert statement.
我发现该变量是必需的 - 您不能直接在插入语句中访问 DELETING。
回答by Tony Andrews
WOW, You want to have only ONE insert in your trigger to avoid what?
哇，你只想在触发器中插入一个来避免什么？
"I have a single insert statement INSERT INTO HIST ( EMP_ID, NAME ) VALUES (:NEW.EMP_ID , :NEW.NAME ) ; when deleting though, I want to use :OLD , not not have a seperate insert statement for that. "
“我有一个插入语句 INSERT INTO HIST (EMP_ID, NAME) VALUES (:NEW.EMP_ID, :NEW.NAME) ；虽然删除时，我想使用 :OLD ，而不是没有单独的插入语句。“
It's a wide table. SO? It's not like there no REPLACE in text editors, you're not going to write the Insert again, just copy, paste, select, replace :NEW with :OLD.
这是一张宽桌。所以？它不像文本编辑器中没有 REPLACE，您不会再次编写 Insert，只需复制、粘贴、选择、将 :NEW 替换为 :OLD。
Tony does have a solution but I seriously doubt that performs better than 2 inserts would perform.
Tony 确实有一个解决方案，但我严重怀疑其性能是否比 2 个刀片更好。
What's the big deal?
有什么大不了的？
EDIT
编辑
the main thing I'm trying to avoid is having to managed 2 inserts when the table changes. – Matthew Watson
我试图避免的主要事情是在表更改时必须管理 2 个插入。— 马修·沃森
I battle this attitude all the time. Those who write Java or C++ or .Net have a built-in RBO... Do this, this is good. Don't do that, that's bad. They write code according to these rules and that's fine. The problem is when these rules are applied to databases. Databases don't behave the same way code does.
我一直在与这种态度作斗争。那些编写 Java 或 C++ 或 .Net 的人有一个内置的 RBO……这样做，这很好。不要那样做，那很糟糕。他们根据这些规则编写代码，这很好。问题是这些规则何时应用于数据库。数据库的行为方式与代码不同。
In the code world, having essentially the same code in two "places" is bad. We avoid it. One would abstract that code to a function and call it from the two places and thus avoid maintaining it twice, and possibly missing one, etc. We all know the drill.
在代码世界中，在两个“地方”拥有本质上相同的代码是不好的。我们避免它。人们会将该代码抽象为一个函数并从两个地方调用它，从而避免维护它两次，并且可能会丢失一个，等等。我们都知道这个练习。
In this case, while it's truethat in the end I recommend two inserts, they are separated by an ELSE. You won't change one and forget the other one. IT'S Right There. It's not in a different package, or in some compiled code, or even somewhere else in the same trigger. They're right beside each other, there's an ELSE and the Insert is repeated with :NEW, instead of :OLD. Why am I so crazed about this? Does it really make a difference here? I know two inserts won't be worse than other ideas, and it could be better.
在这种情况下，虽然最后我确实推荐了两个插入，但它们由 ELSE 分隔。你不会改变一个而忘记另一个。它就在那里。它不在不同的包中，也不在某些编译的代码中，甚至不在同一个触发器中的其他地方。它们就在彼此的旁边，有一个 ELSE，插入重复使用 :NEW，而不是 :OLD。为什么我对这个如此着迷？这里真的有区别吗？我知道两个插入不会比其他想法更糟糕，它可能会更好。
The real reason is being prepared for the times when it does matter. If you're avoiding two inserts just for the sake of maintenance, you're going to miss the times when this makes a HUGE difference.
真正的原因是为重要的时代做好准备。如果您只是为了维护而避免使用两个插件，您将错过这会产生巨大差异的时间。
INSERT INTO log
SELECT * FROM myTable
WHERE flag = 'TRUE'
ELSE -- column omitted for clarity
INSERT INTO log
SELECT * FROM myTable
WHERE flag = 'FALSE'
Some, including Matthew, would say this is bad code, there are two inserts. I could easily replace 'TRUE' and 'FALSE' with a bind variable and flip it at will. And that's what most people would do. But if True is .1% of the values and 99.9% is False, you want two inserts, because you want two execution plans. One is better off with an index and the other an FTS. So, yes, you do have two Inserts to maintain. That's not always bad and in this case it's good and desirable.
有些人，包括马修，会说这是糟糕的代码，有两个插入。我可以轻松地用绑定变量替换 'TRUE' 和 'FALSE' 并随意翻转它。这就是大多数人会做的事情。但是，如果 True 是值的 0.1%，而 99.9% 是 False，则您需要两次插入，因为您需要两个执行计划。一个最好使用索引，另一个使用 FTS。所以，是的，您确实有两个插入要维护。这并不总是坏事，在这种情况下它是好的和可取的。
回答by Khb
You can use a compound trigger and programmatically check if it us I/U/D.
您可以使用复合触发器并以编程方式检查它是否为 I/U/D。
回答by David Aldridge
Why don't you use Oracle's built in standard or fine-grained auditing?
为什么不使用 Oracle 内置的标准或细粒度审计？
回答by AndyDan
Use a compound trigger, as others have suggested. Save the old or new values, as appropriate, to variables, and use the variables in your insert statement:
正如其他人所建议的那样，使用复合触发器。根据需要将旧值或新值保存到变量中，并在插入语句中使用这些变量：
declare
v_col1 table_name.col1%type;
v_col2 table_name.col2%type;
begin
if deleting then
v_col1 := :old.col1;
v_col2 := :old.col2;
else
v_col1 := :new.col1;
v_col2 := :new.col2;
end if;
insert into audit_table(col1, col2)
values(v_col1, v_col2);
end;
