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