Re: 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