PLSQL :NEW and :OLD

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.


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

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

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

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);

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

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

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


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