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:
- INSERT- :old.value= NULL, :new value= post insert value
- DELETE- :old.value= Pre Delete value, :new value= null
- 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