在 Oracle 插入触发器的 WHEN 原因中，对 OLD 的引用的计算结果是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1380139/
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
What do references to OLD evaluate to in the WHEN cause of an Oracle insert trigger?
提问by jlc
When writing a row-level trigger in Oracle, I know that you can use the OLDand NEWpseudo-records to reference the old and new state of the row that fired the trigger.
I know that in an INSERTtrigger OLDdoesn't contain any data, but I'm not sure how this affects the evaluation of a WHENclause for that trigger. For example, if I have the following trigger:
在 Oracle 中编写行级触发器时，我知道您可以使用伪记录OLD和NEW伪记录来引用触发触发器的行的旧状态和新状态。
我知道在INSERT触发器OLD中不包含任何数据，但我不确定这会如何影响WHEN对该触发器的子句的评估。例如，如果我有以下触发器：
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
BEGIN
:NEW.foo = 'some val';
END;
and I want to modify this trigger to only run on an update when foo was previously null, but always run on an insert, I could satisfy the update part of the change by adding a WHEREclause:
并且我想修改此触发器以仅在 foo 以前为 null 时在更新上运行，但始终在插入时运行，我可以通过添加WHERE子句来满足更改的更新部分：
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
WHEN (OLD.foo IS NULL)
BEGIN
:NEW.foo = 'some val';
END;
Will this cause problems in the insert case? What will OLD.foo evaluate to in the INSERT?
这会导致插入案例出现问题吗？OLD.foo 在INSERT?
I'm aware that I could split the INSERTand UPDATEtriggers or use INSERTING/UPDATING/DELETINGin the trigger body, but I'd rather not in the case that inspired this question.
我知道，我可能分裂INSERT和UPDATE触发器或使用INSERTING/ UPDATING/DELETING在触发身体，但我宁愿不要在启发这个问题的情况。
回答by Adam Paynter
When a record is being inserted, every field of OLDwill be NULL, including the fields marked as NOT NULLin the table's definition.
当插入一条记录时， 的每个字段都OLD将是NULL，包括NOT NULL在表定义中标记的字段。
For example, suppose your table has a non-nullable column named id:
例如，假设您的表有一个名为 的不可为空的列id：
CREATE TABLE some_table (
id NUMBER NOT NULL,
foo VARCHAR2(100)
)
When a record is inserted into this table, OLD.idwill be NULL. However, when a record is updated in this table, OLD.idwill not be NULL. Because you only want to change :NEW.fooif a record is being updated, you just have to check to see if OLD.idhas a non-null value.
当一条记录插入到这个表中时，OLD.id将是NULL. 但是，当更新此表中的记录时，OLD.id将不会是NULL。因为您只想:NEW.foo在更新记录时进行更改，所以您只需检查是否OLD.id有非空值。
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
WHEN (OLD.id IS NOT NULL AND OLD.foo IS NULL)
BEGIN
:NEW.foo = 'some val';
END;
