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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] Puolesta Tomasz Ostrowski Lähetetty: 28. maaliskuuta 2008 18:20 Vastaanottaja: Teemu Juntunen Kopio: pgsql-general@xxxxxxxxxxxxxx Aihe: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
