PLSQL :NEW and :OLD

Question

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

Answer 1

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.

Answer 2

: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