Geek asks and answers

New and old trigger code

can someone explain what is meant by:

:newand :oldin the trigger code.

+6
source share
4 answers

:newand :old- pseudo-records that allow you to access the new and old values ​​of specific columns. If i have a table

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

and I insert a line

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

then at line level until the trigger starts

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

and

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

If you then update this line

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

then in the level trigger before the update

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

and

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

If I then delete the line

DELETE FROM foo
 WHERE foo_id = 1

and then in the level trigger before deleting,

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

and

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

In plain English:

These are aliases that allow you to access information about how the column was (old) and how it will be (new).

From the Oracle documentation:

, . BEFORE, AFTER ( AFTER). BEFORE new.column, AFTER, , , BEFORE.

WHEN. , , WHEN REFERENCING.

+3

:old , :new .

+2

the value oldis the value before the change, and the value newis value to be changed, therefore, for example, at update set col1=10, 10 is the value new, and the value that is current in the column is old.

The insert does not have the old value, only the new one, and when deleted, there is no new value, only the old

+2
source

More articles:


All Articles