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.

Nuno

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.

Cinchonize

Are they just useful to display values after and before trigger is fired or is their any other usage? – Nuno

@Nuno common use cases are validation and auditing. Using the above example imagine the requirement was to track an employees salary history, in this case we could insert the old salary, modification date and user name into a history table. In the application I'm working on ATM a trigger is used to update a records elevation when the coordinates are edited – Cinchonize

And they are not just for reference too: in a before insert or update trigger a value could be assigned to the :new values. A common use is to provide a primary key with a value from a sequence in a before-insert trigger. – Explanation

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

Spreader

Answer 3

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

Sexagenary

This behavior of OLD being null in insert and NEW being null in delete changes if the statement is using AFTER instead of BEFORE? – Biota

@Biota No it does not. In BEFORE (insert or update) triggers you are allowed to modify the values, though, and the modifications will end up in the saved record. – Byran

Answer 4

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

Thirzia

Answer 5

:old is your old value :new is your new value,

it is used alot in triggers for example with Creation_Date & Modified_By fields

Miosis

Answer 6

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

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

for example:

CREATE OR REPLACE TRIGGER mytrig BEFORE
  INSERT OR
  UPDATE
    ON mytab FOR EACH ROW
BEGIN
  IF INSERTING THEN
    SELECT trunc(sysdate), trunc(sysdate) INTO :new.created, :NEW.last_updated FROM DUAL;
  END IF; --INSERTING

  IF UPDATING THEN

      SELECT trunc(sysdate) INTO :NEW.last_updated FROM DUAL;

  END IF; --UPDATING

END;

Hope this explains the concept of old and new.

Konrad

Answer 7

:new is the new value - After the trigger is fired this is the value of the column :old is the old value - After the trigger is fired this value is replaced with :new value

Sezen

Answer 8

New and Old more relevant for update operation inside a trigger, to fetch old value of field use old and for recent value use new

Unteach

Answer 9

In simple way,

Trigger will fire when you manipulate data into table. so while trigger invoke, you have both value. one is referring to old data value and one is the new data value which you recently update/delete/insert. in-case of

insert- old value would be null and new value contain some value update - old and new both have some value delete - old has value but new will not contain value.

so by using :OLD and :NEW, you can insert/update other table where you want to maintain your history or based on :OLD or :NEW value you can insert/update some other dependent table.

Hopefully this helps you..

Fortna

Answer 10

It's very simple

If Insert :old = NULL and :New = New Inserted Value
If Update  :Old = Value already in the table  :New = Updated Value in the Table
If Delete :Old = Value before deletion :New = NULL

IN other Words

1. For an INSERT trigger, OLD contains no values, and NEW contains the new values.

2. For an UPDATE trigger, OLD contains the old values, and NEW contains the new values.

3. For a DELETE trigger, OLD contains the old values, and NEW contains no values.

Example:

CREATE OR REPLACE TRIGGER update_name_view_trigger
INSTEAD OF UPDATE ON emp_locations
BEGIN
  UPDATE employees SET
    first_name = substr( :NEW.name, instr( :new.name, ',' )+2),
    last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1)
  WHERE employee_id = :OLD.employee_id;
END;

Nutria

Answer 11

:OLD and :NEW are variables of type Record and is identical in columns to the table row on which the trigger is fired. They are only available for row level triggers. Since triggers are fired on a predefined event automatically so :OLD and :NEW also get their value automatically . As the name suggests , :OLD will have the complete row values existing in table ( i.e has the existing values in case of update and delete ) and :NEW will have new values for that rows ( i.e. has the row value in case of update and insert ).

Hildegaard

Answer 12

A simple example that shows the use of old and new using triggers

CREATE TABLE emp_log(
emp_id NUMBER;
updated_by DATE,
new_salary VARCHAR2(15),
Action VARCHAR2(20));

CREATE OR REPLACE TRIGGER log_sal 
AFTER UPDATE OF sal on emp
FOR EACH ROW
BEGIN
INSERT INTO emp_log( emp_id, updated_by, new_salary, Action)
VALUES(:NEW.empno, USER, :NEW.sal, 'New salary');
END;
/

Labdanum

Answer 13

You can use :OLD while working on after/before update 100% of time :NEW is a record of the new value added by (insert or update) :OLD is for old values. Without them, triggers are useless

Giraffe

Answer 14

Mainly here is the words refers

:old refer to the old value before trigger fire. :new contain the value of after trigger fire.

Natality

Answer 15

You may get this when trying to do any database modifications(Updates). When you're inserting(adding/new) data :New will appear. Since you don't any existing data nothing is showing as :Old. When you're updating existing details through the code you will see the existing data as :Old and the updated details as :New.

Fortunato

Answer 16

Within the trigger body, the :OLD and :NEW keywords enable you to access columns in the rows affected by a trigger

:OLD is used to get the old value of the column before it gets updated or deleted. Therefore OLD is used only with delete or update statements triggers

:NEW is used to get the value that is updated or just inserted in the database. Therefore NEW is used with the only update and insert statements triggers

Getty

Answer 17

The restrictions on pseudorecords are:

• A pseudorecord cannot appear in a record-level operation.

For example, the trigger cannot include this statement:

:NEW := NULL;

• A pseudorecord cannot be an actual subprogram parameter.

(A pseudorecord field can be an actual subprogram parameter.)

• The trigger cannot change OLD field values.

Trying to do so raises ORA-04085.

• If the triggering statement is DELETE, then the trigger cannot change NEW field values.

Trying to do so raises ORA-04084.

• An AFTER trigger cannot change NEW field values, because the triggering statement runs before the trigger fires.

Trying to do so raises ORA-04084.

• A BEFORE trigger can change NEW field values before a triggering INSERT or UPDATE statement puts them in the table.
• If a statement triggers both a BEFORE trigger and an AFTER trigger, and the BEFORE trigger changes a NEW field value, then the AFTER trigger "sees" that change.

Trilbi