CODEWITHSUNDEEP
postgresqlpostgresql-11
JC Boggio
JC Boggio

Reputation: 407

Trigger on self-referenced table

I have an invoices table like this :

CREATE TABLE invoices (
    idinvoice SERIAL PRIMARY KEY
    ,idclient INTEGER REFERENCES clients(idclient)
    -- other fields here
);

and an invoiceitems table :

CREATE TABLE invoiceitems (
    idinvoiceitem SERIAL PRIMARY  KEY
    ,idinvoice INTEGER REFERENCES invoices(idinvoice)
    ,amount NUMERIC(15,2)
    ,sortorder INTEGER DEFAULT 10
);

My problem is with the sortorder field : every time I insert or update an invoiceitem, I want the sortorders of all items of the same invoice to be recalculated with a query like this :

WITH pre AS (
    SELECT idinvoiceitem,10*ROW_NUMBER() OVER (ORDER BY sortorder,idinvoiceitem) AS rownum
    FROM invoiceitems
    WHERE idinvoice=xx
    ORDER BY sortorder,idinvoiceitem
)
UPDATE invoiceitems di
SET sortorder=rownum
FROM pre p
WHERE p.idinvoiceitem=di.idinvoiceitem;

That way, every time a new item is added, it will be numbered 10+last item, and if I decide to change the sort order of the item by manually setting a value, all the sortorders will be recalculated with a 10 increment :

id | sortorder
---+----------
 1 | 10
 2 | 20
 3 | 30
 4 | 40  ← I change this to 25

Then after the query, I will get :

id | sortorder
---+----------
 1 | 10
 2 | 20
 4 | 30
 3 | 40

I would like to have this in a trigger. The problem is that item #4 will be UPDATEd by the trigger (thus calling the trigger again) and I will get some kind of endless recursion.

Is there a way to do this ? For example, set some semaphore that would prevent the trigger from being called when the UPDATE occurs within the trigger itself ? Or maybe an explicit LOCK ?

(using PostgreSQL 11+)

Upvotes: 2

Views: 185

Answers (1)

ginkul
ginkul

Reputation: 1066

One thing you could do is to use pg_trigger_depth() function, which is documented here.

Then your trigger should look similar to this:

CREATE TRIGGER trigger_name
AFTER UPDATE OR INSERT ON invoiceitem
FOR EACH ROW
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE your_procedure();

I'd recommend read some discussions about this approach (1, 2), but in general for not complicated systems this should be fine.

Upvotes: 1

Related Questions