VS: Delete after trigger fixing the key of row numbers

Hi Tomasz,

with receipt rows I can't see any other option with key as running number.
First I tried to use serial, but it didn't start from zero again when the
foreign key (receipt number) changes. It just continues the serial despite
of the foreign key.

Then I decided to do my own serial which keeps it in order even when some
rows are deleted from the middle. Of course I could just leave the row
numbers as is, but there are some advantages at the program, if the serial
is linear. What is the danger you see in this row number update trigger?

Richard, thank you for your great answer with many solutions!

Best regards,
Teemu Juntunen

-----Alkuperäinen viesti-----
Lähettäjä: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Puolesta Tomasz Ostrowski
Lähetetty: 28. maaliskuuta 2008 18:20
Vastaanottaja: Teemu Juntunen
Kopio: pgsql-general(at)postgresql(dot)org
Aihe: Re: [GENERAL] Delete after trigger fixing the key of row numbers

On 2008-03-28 13:27, Teemu Juntunen wrote:

> I am developing an ERP to customer and I have made few tables using a
> row number as part of the key. When deleting a line from such a
> table, I have made an after delete trigger, which fixes the row
> numbers with following command:
> UPDATE orderrow SET row = row - 1 WHERE order = old.order AND row >
old.row;

Whoa! That was a very bad design decision. This will eat your data
sooner or later.

> It seems like it tries to do the change in wrong order at the receipt
table.

You can force an order of updates using a loop in plpgsql. But you
should redesign your database before it is too late.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh