Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

new and old trigger code

can someone please explain what is meant by:

:new and :old in trigger code.

like image 767
user1300580 Avatar asked May 17 '12 16:05

user1300580


2 Answers

: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'
like image 168
Justin Cave Avatar answered Oct 02 '22 11:10

Justin Cave


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.

like image 37
RThomas Avatar answered Oct 02 '22 11:10

RThomas