Re: Delete after trigger fixing the key of row numbers

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