Delete after trigger fixing the key of row numbers
Greetings from Finland to everyone!
I joined the list to hit you with a question ;)
I am developing an ERP to customer and I have made few tables using a row
number as part of the key. Frex. Order rows table has a key of order number
and row number like Receipt rows table has a key of Receipt number and row
number.
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;
Receiptrow table has a similiar trigger
UPDATE receiptrow SET row = row - 1 WHERE receipt = old.receipt AND row >
old.row;
My problem is that this command works fine on the orderrow table, but it
gives an duplicate key violation error on the receipt table when there is at
least two rows to be changed. It seems like it tries to do the change in
wrong order at the receipt table. Frex. if I have 3 rows and I am deleting
the first, it tries to change row number 3 to row number 2 first giving a
duplicate error. I reindexed the receipt table with no help. I tried
followin trigger with no help:
UPDATE receiptrow SET row = row - 1 WHERE row in (SELECT row FROM receiptrow
WHERE receipt = old.receipt AND row > old.row ORDER BY row ASC);
and I tried the order of DESC too. Does anyone have a glue what might be
wrong?
The keys of the tables are not so similar as in my example. Correct keys are
Order row:
1. Shop code
2. Order number
3. Row number
Receipt row:
1. Shop code
2. Date
3. Cash desktop code
4. Receipt number
5. Row number
I am using PostgreSQL 8.2.5 at the moment.
Best regards,
Teemu Juntunen
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
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Puolesta Tomasz Ostrowski
Lähetetty: 28. maaliskuuta 2008 18:20
Vastaanottaja: Teemu Juntunen
Kopio: pgsql-general@postgresql.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
--
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