new and old trigger code

sql triggers oracle10g

36,753

Solution 1

:new and :old are pseudo-records that let you access the new and old values of particular columns. If I have a table

CREATE TABLE foo (
  foo_id NUMBER PRIMARY KEY,
  bar    VARCHAR2(10),
  baz    VARCHAR2(10)
);

and I insert a row

INSERT INTO foo( foo_id, bar, baz ) 
  VALUES( 1, 'Bar 1', 'Baz 1' );

then in a row-level before insert trigger

:new.foo_id will be 1
:new.bar will be 'Bar 1'
:new.baz will be 'Baz 1'

while

:old.foo_id will be NULL
:old.bar will be NULL
:old.baz will be NULL

If you then update that row

UPDATE foo
   SET baz = 'Baz 2'
 WHERE foo_id = 1

then in a before update row-level trigger

:new.foo_id will be 1
:new.bar will be 'Bar 1'
:new.baz will be 'Baz 2'

while

:old.foo_id will be 1
:old.bar will be 'Bar 1'
:old.baz will be 'Baz 1'

If I then delete the row

DELETE FROM foo
 WHERE foo_id = 1

then in a before delete row-level trigger,

:new.foo_id will be NULL
:new.bar will be NULL
:new.baz will be NULL

while

:old.foo_id will be 1
:old.bar will be 'Bar 1'
:old.baz will be 'Baz 2'

Solution 2

In Plain English:

They are aliases that allow you to access information from how a column was (old) and how it will be (new).

From Oracle documentation:

Old and new values are available in both BEFORE and AFTER row triggers. A new column value can be assigned in a BEFORE row trigger, but not in an AFTER row trigger (because the triggering statement takes effect before an AFTER row trigger is fired). If a BEFORE row trigger changes the value of new.column, then an AFTER row trigger fired by the same statement sees the change assigned by the BEFORE row trigger.

Correlation names can also be used in the Boolean expression of a WHEN clause. A colon must precede the old and new qualifiers when they are used in a trigger's body, but a colon is not allowed when using the qualifiers in the WHEN clause or the REFERENCING option.

Solution 3

In a trigger the :old record contains the values before the triggering statement's execution, the :new record contains the values after the execution.

Solution 4

the old value is the value before the change and the new value is the value to be changed, so for example, on a update set col1=10, 10 is the new value and the value that is current in the column is the old on.

On a insert, there isnt old value, only new and on a delete there isn't new value, only old

View more solutions

36,753