Re: :old in triggers

system_at_phaxp2.ab.umd.edu wrote:

: Help!
: Hi. I am trying to determine how to create
: an ORACLE trigger such that whenever a record
: is inserted, all the values for a primary key field
: get incremented by 1, depending on certain criteria.
: Example, the following will not work but represents
: what I am trying to do:
 

: create trigger trig1 before insert on junk
: for each row when (new.f1 = old.f1
: and new.f2 = old.f2)
: begin
: :old.f3 := :old.f3 + 1;
: end;
: /
 

: This fails because :old fields cannot get reassigned.
: Perhaps there is a way to do this with an embedded SQL UPDATE.
: Thank you.
 

: Steve Skarupa
: skarupa_at_pharmvax1.ab.umd.edu

If you want to change the primary key value for the current row, replace the
: :old.f3 := :old.f3 + 1; assignment with
: :new.f3 := :new.f3 + 1;

No "old" value exists for a row being inserted.

You cannot update the same table on which a row trigger is executing, since the table is "mutating".

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
---------------------------------------------------------------------------