Postgresql: create trigger to increment count_column between two tables?

Question

I have a table called car and another table called inventory.

car has a column called needs_oil_change (bool)

inventory has a column called oil_change_due_count INTEGER

I want to create a trigger that will incremented or decrement the inventory.oil_change_due_count whenever a car.oil_change_due_count is changed (or whenever a new car record is inserted, or deleted).

So, in short, I want a trigger to keep the inventory summary count column oil_change_due_count synchronized as car records are created/deleted/updated.

I've tried to follow some docs online like these:

https://w3resource.com/PostgreSQL/postgresql-triggers.php

https://dataegret.com/2017/10/postgresqls-transition-relations/

and others.

I haven't been able to create one that works yet.

How would I write a trigger that could handle that logic?

I just found this:

PostgreSQL: Checking for NEW and OLD in a function for a trigger

not sure if it will answer my question, but I will try to learn from that and see if I can apply to my question.

I think this is going to need the schema of the `car` and `inventory` tables. Or at least more explanation on the relationship between `car.needs_oil_change`, `inventory.oil_change_due_count and `car.oil_change_due_count`. In particular whether there is field in `inventory` that relates back to `car`? — Adrian Klaver, Oct 02 '20 at 23:12
car has an inventory_id FK. Inventory table does not have a car FK. So, car points to inventory. That’s how it’s laid out now. — zumzum, Oct 03 '20 at 01:12

score 2 · Answer 1 · answered Oct 04 '20 at 16:36

First of all you need to create a trigger function to handle the logic, in postgresql you can do something like this

CREATE OR REPLACE FUNCTION fn_oil_change() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE inventory SET oil_change_due_count = oil_change_due_count - OLD.oil_change_due_count;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            ... some logic to hangle update 
            RETURN NEW
        ELSIF (TG_OP = 'INSERT') THEN
            ... some logic to hangle update
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$$ LANGUAGE plpgsql;

and then create the trigger itself

CREATE TRIGGER tg_oil_change
AFTER INSERT OR UPDATE OR DELETE ON car
    FOR EACH ROW EXECUTE PROCEDURE fn_oil_change();

Something more or less like this, right now I can't test the code, and you'll need to think how to handle the update.

yes, this looks great. I just wrote the trigger that handles updates and I tested it in data grip. Works great. Now I'll look at your example for the DELETE case. — zumzum, Oct 04 '20 at 19:00

Postgresql: create trigger to increment count_column between two tables?

1 Answers1