can someone please explain what is meant by:
:new
and :old
in trigger code.
: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'
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With