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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Teemu Juntunen, e-ngine wrote:
> 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?
>

If you'd like to have a linear count of your result set it shouldn't be
difficult to implement that in your client-side application code (for
loop, for instance). You don't need to use the serial for display
purposes but it's very helpful for maintaining intact relations.

Besides, if this table has any associations with others, doing this
would seem to make your archived data next to useless if you ever need it.

b

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general