PLSQL :NEW and :OLD

oracle plsql triggers
Pravin picture Pravin · Oct 30, 2012 · Viewed 87.5k times · Source

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

GrahamA picture GrahamA · Oct 30, 2012

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.

Related questions

pragma autonomous_transaction in a trigger

I have written a trigger on one table which deletes data from other table upon a condition. The trigger has pragma autonomous_transaction, and trigger works as intended. However, I do wonder if there can be any problems in future, …

Read More
Oracle Form FRM-40735: ON-ERROR trigger raised unhandled exception ORA-06502

I have a table trigger like below: CREATE OR REPLACE TRIGGER PAT_BUR_DOB_TRG BEFORE UPDATE OF DOB ON PAT REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW -- PL/SQL Block begin tgln_sys_error_pkg.…

Read More
How Can I code a "IF UPDATING" trigger in Oracle Database 10g?

I'm coding a Trigger to ensure only one type of money can be set as official. My intention is code a "BEFORE INSERT OR UPDATE" trigger. The INSERT section works fine but the problem is coding the UPDATING section because …

Read More