oracle 触发器中的 :OLD 和 :NEW 变量是什么数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5943548/
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
Of what data type are the :OLD and :NEW variables in a trigger?
提问by Adam Paynter
Suppose you have a trigger on MY_CUSTOMER_TABLEand that it has a variable declared of type MY_CUSTOMER_TABLE%ROWTYPE. How can I assign the OLDvalue into that variable?
假设您有一个触发器，MY_CUSTOMER_TABLE并且它有一个声明为 type 的变量MY_CUSTOMER_TABLE%ROWTYPE。如何将OLD值分配给该变量？
CREATE TRIGGER CUSTOMER_BEFORE
BEFORE UPDATE ON MY_CUSTOMER_TABLE
FOR EACH ROW
DECLARE
old_version MY_CUSTOMER_TABLE%ROWTYPE;
BEGIN
old_version := :OLD; /* Causes a PLS-00049 bad bind variable 'OLD' */
old_version := OLD; /* Causes a PLS-00201 identifier 'OLD' must be declared */
END;
Edit:
编辑：
To clarify, this came about because I am using triggers to archive rows from MY_CUSTOMER_TABLEinto MY_CUSTOMER_TABLE_HISTORY. Depending upon the action being performed (INSERT, UPDATE, DELETE), I need all the fields from either OLDor NEW:
澄清一下，这是因为我使用触发器将行从MY_CUSTOMER_TABLEinto存档MY_CUSTOMER_TABLE_HISTORY。根据正在执行的操作 ( INSERT, UPDATE, DELETE)，我需要来自OLD或 的所有字段NEW：
CREATE TRIGGER CUSTOMER_BEFORE
BEFORE UPDATE ON MY_CUSTOMER_TABLE
FOR EACH ROW
DECLARE
historical_record MY_CUSTOMER_TABLE_HISTORY%ROWTYPE;
PROCEDURE
copy
(
source_record MY_CUSTOMER_TABLE%ROWTYPE,
destination_record IN OUT MY_CUSTOMER_TABLE_HISTORY%ROWTYPE
)
BEGIN
destination_record.customer_id := source_record.customer_id;
destination_record.first_name := source_record.first_name;
destination_record.last_name := source_record.last_name;
destination_record.date_of_birth := source_record.date_of_birth;
END;
BEGIN
/* I didn't want to replicate the same assignment statements for
each of the two cases: */
CASE
WHEN INSERT OR UPDATING THEN
copy( source_record => :NEW, destination_record => historical_record );
WHEN DELETING THEN
copy( source_record => :OLD, destination_record => historical_record );
END CASE;
/* Some other assignments to historical_record fields... */
INSERT INTO MY_CUSTOMER_TABLE_HISTORY VALUES historical_record;
END;
In this scenario, PL/SQL will not let me pass :OLDor :NEWto a procedure that expects a MY_CUSTOMER_TABLE%ROWTYPEargument.
在这种情况下，PL/SQL 不会让我传递:OLD或传递:NEW到需要MY_CUSTOMER_TABLE%ROWTYPE参数的过程。
采纳答案by Eddy B
From what I can determine, the definition of what :NEW and :OLD really are IS a little vague. I have seen it referred to as a reference to a "pseudo-record". It would seem though, that instead of an actual rowtype with each column being reference-able within the rowtype, Oracle sets up a reference to each individual column, which you then reference by using :NEW and :OLD. But, as you are finding out, :NEW and :OLD don't seem to be referenceable by themselves.
据我所知， :NEW 和 :OLD 的定义确实有点模糊。我已经看到它被称为对“伪记录”的引用。看起来，不是每个列都可以在行类型中引用的实际行类型，而是 Oracle 设置对每个单独列的引用，然后您可以使用 :NEW 和 :OLD 进行引用。但是，正如您所发现的， :NEW 和 :OLD 本身似乎不可引用。
For example, here. (yes yes, I know, it's a Java reference, but see the comment about :OLD not being a valid reference by itself.
例如，这里。（是的，我知道，这是一个 Java 参考，但请参阅关于 :OLD 本身不是有效参考的评论。
I also found this note SYS.DBMS_DEBUGpackage which implies that :NEW/:OLD is not a valid bind either.
我还发现了这个注释SYS.DBMS_DEBUG包，这意味着 :NEW/:OLD 也不是有效的绑定。
-- get_value and set_value now support bind names. Bind names must be -- put in quotes and capitalized. Note that trigger binds have -- qualified names, i.e. ":NEW" is not a valid bind, while ":NEW.CLMN" -- is valid.
-- get_value 和 set_value 现在支持绑定名称。绑定名称必须 -- 放在引号中并大写。请注意，触发器绑定具有 -- 限定名称，即 ":NEW" 不是有效绑定，而 ":NEW.CLMN" -- 有效。
Would thissuggestion of using an AFTERtrigger work for you? From your example, it doesn't seem that there is any validation happening on the values (realizing you may not have put that into your example for simplicity).
请问该用的建议AFTER对您触发工作方式？从您的示例中，似乎没有对值进行任何验证（意识到为了简单起见，您可能没有将其放入示例中）。
I was trying to envision a way to construct a public type on the fly (within your trigger) that would match your table rowtype using the all_tab_columns view, and then stuff all the values into it, but can't quite wrap my head around the details of how that might shake out...if it would even work. And it likely ends up being more work than required to log a historical record!
我试图设想一种方法来动态构建一个公共类型（在你的触发器内），它会使用 all_tab_columns 视图匹配你的表行类型，然后把所有的值都塞进去，但不能完全理解可能会如何动摇的细节......如果它甚至有效的话。并且它可能最终比记录历史记录所需的工作更多！
回答by Gary Myers
You can't. It is generally bad practice to refer to all columns (like SELECT *) and you should specify the columns you need.
你不能。引用所有列（如 SELECT *）通常是不好的做法，您应该指定所需的列。
回答by Rene
In the docs you will find that :old and :new are column values, not row types. So you will have to construct your rowtype manually.
在文档中，您会发现 :old 和 :new 是列值，而不是行类型。因此，您必须手动构建行类型。
trigger ....
l_row mytable%rowtype;
begin
l_row.column1 := :old.column1;
l_row.column2 := :old.column2;
...
archive_function(l_row);
end;
