Updating with indexes
Date: Fri, 9 Jul 1993 20:09:36 GMT
Message-ID: <1993Jul9.200936.18890_at_cc.umontreal.ca>
Fields: ID1 NOT NULL NUMBER(10) ID2 NOT NULL NUMBER(10) ID3 NOT NULL NUMBER(10) COST NUMBER
ID1, ID2 and ID3 are the key components.
In a KEY-CREREC trigger we have the following piece of SQL:
UPDATE A
SET ID3 = ID3 + 1,
WHERE ID1 = :BLOCKA.VAL1 AND
ID2 = :BLOCKA.VAL2 AND
ID3 > :BLOCKB.VAL1;
Before the creation of the index for table A, that piece of code worked exactly as we wanted it to. That is, it incremented the key value for ID3 to allow the insertion of a new record at the position indicated by BLOCKB.VAL1. After the creation of the index for the table, then we got:
FRM-40508: ORACLE error -- unable to INSERT record.
Caused by a:
ORA-00001: Duplicate key in index.
We looked for a way to do the incrementation with an "order by ID3 desc", but there's no way to tell Oracle how to do its update.
We tried to make the update in two pass. Something like:
Pass 1:
update A
set ID3 = ID3 + 1,
ID1 = "A temporary special value"
Pass 2:
update A
set ID1 = "The right value"
But it involves a lot of troubles to protect the integrity of the DB if many users are working on the same set of data.
So what are you suggesting to solve that problem ? Any help would be greatly appreciated !
Daniel Cote /\
Universite de Montreal |--| coted_at_jsp.umontreal.ca
Montreal, Quebec \/ coted_at_ere.umontreal.ca
Received on Fri Jul 09 1993 - 22:09:36 CEST