Re: Updating with indexes

From: <champs_at_cnb07v.hhcs.gov.au>
Date: 12 Jul 93 09:19:07 +1000
Message-ID: <1993Jul12.091907.1_at_cnb07v.hhcs.gov.au>

In article <1993Jul9.200936.18890_at_cc.umontreal.ca>, coted_at_ERE.UMontreal.CA (Cote Daniel) writes:
> We have problems in updating a key component of a record in a table
> for which an index as been created. The context is:
>
> Table: A
> 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.
>

[ stuff deleted ]

>
> Daniel Cote /\
> Universite de Montreal |--| coted_at_jsp.umontreal.ca
> Montreal, Quebec \/ coted_at_ere.umontreal.ca

As you're already doing all this in a trigger, here's some code that should do the trick:

DECLARE
  cursor update_stuff is
    select ID3
    from A

    where  ID1 = :BLOCKA.VAL1
      and  ID2 = :BLOCKA.VAL2
      and  ID3 > :BLOCKB.VAL1

    order by ID3 desc
    for update of ID3;

    junk number;
BEGIN
    open update_stuff;
    fetch update_stuff into junk;

    while update_stuff%found loop

      update a
      set ID3 = ID3 + 1
      where current of update_stuff;

      fetch update_stuff into junk;

    end loop;

    close update_stuff;
END; Good luck.
Bye for now.

---

    /\       Steven Champness (champs_at_cnb07v.hhcs.gov.au)
   /  \      Dept of Health, Housing, Local Govt and Community Services
  /    \     Brisbane, Queensland via Canberra, Australia
 /      \    

/ / \ The opinions expressed above were forcibly implanted in my head \ / / / by aliens from Alpha Zeltex 4, during a recent Invasion attempt.  \/ / / /

  \/ / /     MACINTOSH      : If you need a manual to operate it, it wasn't
   \/ /      SOFTWARE AXIOM : designed properly in the first place !!!!

    \/ Received on Mon Jul 12 1993 - 01:19:07 CEST