CODEWITHSUNDEEP
sqltriggersoracle10g
user1300580
user1300580

Reputation: 419

new and old trigger code

can someone please explain what is meant by:

:new and :old in trigger code.

Upvotes: 9

Views: 44981

Answers (4)

Diego
Diego

Reputation: 36146

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

Upvotes: 2

RThomas
RThomas

Reputation: 10882

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.

Upvotes: 5

Szilard Barany
Szilard Barany

Reputation: 1135

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

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231671

: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'

Upvotes: 41

Related Questions