Teemu Juntunen, e-ngine wrote:
Greetings from Finland to everyone!
On behalf of everyone, hello Finland.
I joined the list to hit you with a question ;)
That's what it's there for.
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.
OK
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.
Known problem, I'm afraid. It's because the unique constraint is
enforced by a unique index and that doesn't allow the test to be
deferred until the end of the command, so processing order matters.
There are three work-arounds:
1. Use -ve numbers as a temporary stage, to avoid the overlap.
UPDATE rr SET row = - (row - 1) WHERE ...
UPDATE rr SET row = - row WHERE row < 0
2. Write your trigger using a loop that goes through renumbering one at
a time, in order.
3. Have an AFTER UPDATE trigger as well as AFTER DELETE
AFTER DELETE:
UPDATE rr SET row=row-1 WHERE ... AND row = (old.row + 1)
AFTER UPDATE:
IF NEW.row = (OLD.row - 1) THEN
UPDATE rr SET row=row-1 WHERE ... AND row = (old.row + 1)
This one ripples through, renumbering.
That any help?
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
