Skip site navigation (1) Skip section navigation (2)

Delete after trigger fixing the key of row numbers

From: "Teemu Juntunen" <teemu(dot)juntunen(at)motonet(dot)fi>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Delete after trigger fixing the key of row numbers
Date: 2008-03-28 12:27:59
Message-ID: 000001c890cf$28e5adb0$7ab10910$@juntunen@motonet.fi (view raw or flat)
Thread:
Lists: pgsql-general
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

 

 

 

 

Responses

pgsql-general by date

Next:From: pgsql-general-ownerDate: 2008-03-28 12:38:21
Subject: [pgsql-general] Daily digest v1.8036 (18 messages)
Previous:From: Stanislav RaskinDate: 2008-03-28 12:12:49
Subject: SELECT DISTINCT ON and ORDER BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group