oracle PLSQL :NEW 和 :OLD

PLSQL :NEW and :OLD

提问by Pravin

Can anyone help me understand when to use :NEWand :OLDin PLSQL blocks, I'm finding it very difficult to understand their usage.

任何人都可以帮助我了解何时使用:NEW和:OLD在 PLSQL 块中，我发现很难理解它们的用法。

回答by GrahamA

You normally use the terms in a triggerusing :oldto reference the old value and :newto reference the new value.

您通常在触发器中使用术语using:old来引用旧值和:new引用新值。

Here is an example from the Oracle documentation linked to above

以下是与上述链接的 Oracle 文档中的示例

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;

In this example the trigger fires BEFORE DELETE OR INSERT OR UPDATE:old.salwill contain the salary prior to the trigger firing and :new.salwill contain the new value.

在此示例中，触发器触发BEFORE DELETE OR INSERT OR UPDATE:old.sal将包含触发器触发之前的薪水，:new.sal并将包含新值。

回答by Gok

:New and :Old Value can be differentiated in DML Statements .
Insert-- :Old= NULL :New= Inserted new value

:New 和 :Old 值可以在 DML 语句中区分。
插入-- :Old= NULL :New= 插入新值

Update-- :Old= Value present in table before the Update statement Triggered :New= Given new value to Update

更新-- :Old= 在 Update 语句触发之前存在于表中的值 :New= 给更新的新值

Delete-- :Old= Value before deletion :New= NULL

删除-- :Old= 删除前的值 :New= NULL

回答by Uday Gupta

:old and :new are pseudorecords referred to access row level data when using row level trigger.

:old 和 :new 是使用行级触发器时访问行级数据的伪记录。

• :old - refers to Old Value
• :new - refers to New value

• :old - 指旧值
• :new - 指新值

For Below operation, respective old and new values:

对于下面的操作，各自的旧值和新值：

1. INSERT- :old.value= NULL, :new value= post insert value
2. DELETE- :old.value= Pre Delete value, :new value= null
3. UPDATE- :old.value= Pre update value, :new value= Post Update value

1. INSERT- :old.value= NULL, :new value= 后插入值
2. DELETE- :old.value= 预删除值，:new value= null
3. UPDATE- :old.value= 更新前值，:new value= 更新后值

Eg:

例如：

CREATE OR REPLACE TRIGGER get_dept
  BEFORE DELETE OR INSERT OR UPDATE ON employees
  FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT('Old Dept= ' || :OLD.dept|| ', ');
  DBMS_OUTPUT.PUT('New Dept= ' || :NEW.dept );
END;

Triggering Statement:

触发语句：

UPDATE employees
SET dept ='Accounts'
WHERE empno IN (101 ,105);

回答by Arun nath

:new means the new value your are trying to insert :old means the existing value in database

:new 表示您尝试插入的新值 :old 表示数据库中的现有值

回答by Jelman

:old is your old value :new is your new value,

:old 是你的旧值 :new 是你的新值，

it is used alot in triggers for example with Creation_Date & Modified_By fields

它在触发器中使用很多，例如 Creation_Date 和 Modified_By 字段

回答by Arjun

:old and :new are pseudo records referred to access row level data when using row level trigger.

:old 和 :new 是在使用行级触发器时引用访问行级数据的伪记录。

?:old - refers to Old Value ?:new - refers to New value

?:old - 指旧值 ?:new - 指新值

for example:

例如：

CREATE OR REPLACE TRIGGER mytrig BEFORE
  INSERT OR
  UPDATE
    ON mytab FOR EACH ROW
BEGIN
  IF INSERTING THEN
    SELECT trunc(sysdate), trunc(sysdate) INTO :new.created, :NEW.last_updated FROM DUAL;
  END IF; --INSERTING

  IF UPDATING THEN

      SELECT trunc(sysdate) INTO :NEW.last_updated FROM DUAL;

  END IF; --UPDATING

END;

Hope this explains the concept of old and new.

希望这可以解释新旧概念。

回答by sudhirkondle

New and Old more relevant for update operation inside a trigger, to fetch old value of field use old and for recent value use new

New 和 Old 与触发器内的更新操作更相关，获取字段的旧值使用 old 和最近值使用 new

回答by harshkumar satapara

In simple way,

简单来说，

Trigger will fire when you manipulate data into table. so while trigger invoke, you have both value. one is referring to old data value and one is the new data value which you recently update/delete/insert. in-case of

当您将数据操作到表中时将触发触发器。所以虽然触发器调用，你有两个价值。一个是指旧数据值，一个是您最近更新/删除/插入的新数据值。的情况下

insert- old value would be null and new value contain some value update - old and new both have some value delete - old has value but new will not contain value.

插入 - 旧值将为空，新值包含一些值更新 - 旧值和新值都有一些值删除 - 旧值但新值不包含值。

so by using :OLD and :NEW, you can insert/update other table where you want to maintain your history or based on :OLD or :NEW value you can insert/update some other dependent table.

因此，通过使用 :OLD 和 :NEW，您可以插入/更新要维护历史记录的其他表，或者基于 :OLD 或 :NEW 值，您可以插入/更新其他一些相关表。

Hopefully this helps you..

希望这对你有帮助..

回答by praveen muppala

:new is the new value - After the trigger is fired this is the value of the column :old is the old value - After the trigger is fired this value is replaced with :new value

:new 是新值 - 触发器被触发后，这是列的值 :old 是旧值 - 触发器被触发后，这个值被替换为：新值

回答by Pawan Rawat

:OLD and :NEW are variables of type Record and is identical in columns to the table row on which the trigger is fired. They are only available for row level triggers. Since triggers are fired on a predefined event automatically so :OLD and :NEW also get their value automatically . As the name suggests , :OLD will have the complete row values existing in table ( i.e has the existing values in case of update and delete ) and :NEW will have new values for that rows ( i.e. has the row value in case of update and insert ).

:OLD 和 :NEW 是 Record 类型的变量，在列中与触发触发器的表行相同。它们仅可用于行级触发器。由于触发器是在预定义事件上自动触发的，因此 :OLD 和 :NEW 也会自动获取它们的值。顾名思义，:OLD 将具有表中存在的完整行值（即在更新和删除的情况下具有现有值），而 :NEW 将具有该行的新值（即在更新和删除的情况下具有行值）插入 ）。