Re: Deleting and updating field after decrementing

Ron K wrote:

> I would appreciate if someone could suggest the best method to
> accomplish the following:
>
> I have a table named 'ITEM' that has the following columns:
>
> Itemno number(03) ---> Key
> Description varchar2(40)
>
> The data is displayed using form 6i in a tabular form.
>
> The field "Itemno" is generated using the trigger 'When new record
> Instance' using the code
>
> select nvl(max(Itemno),0) + 1 from ITEM
> save;
>
> I have 4 set of values displayed on the form as follows:
>
> Itemno Description
> `````````````` ```````````````````
> 1 circle
> 2 square
> 3 triangle
> 4 rectangle
>
> If I delete the row containing "square", I want the "Itemno" of
> "triangle" to be 2, "Itemno" of "rectangle" to be 3 and so on as
> shown below.
>
> Itemno Description
> `````````````` ```````````````````
> 1 circle
> 2 triangle
> 3 rectangle
>
> The data should be displayed as shown above upon deletion.
>
> Will I run into any kind of mutation problems as I am deleting and
> updating a table at the same time?
>
> Thanks in advance...

What you are going to run into is a processing disaster. This can only be done with a stored procedure and will be slow if you have a large table ... and miserable if it is accessed by multiple users at the same time. Lets face it ... primary keys should not be modified: Ever.

So I would suggest that you stop using Itemno as a display item and just leave it alone in your table for connecting as a foreign key to child tables.

For display purposes query like this:

SELECT rownum, Description
FROM item;

rownum is a pseudocolumn that will always assign the number 1 to the first row, 2 to the second, etc.

Daniel A. Morgan Received on Sat Jul 21 2001 - 23:53:19 CEST