PLSQL :NEW and :OLD

oracle, plsql, triggers

Solution

You normally use the terms in a trigger using `:old` to reference the old value and `:new` to reference the new value.

Here is an example from the Oracle documentation linked to above

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.sal` will contain the salary prior to the trigger firing and `:new.sal` will contain the new value.

Problem

Can anyone help me understand when to use `:NEW` and `:OLD` in PLSQL blocks, I'm finding it very difficult to understand their usage.

Original source