CODEWITHSUNDEEP
postgresql
zumzum
zumzum

Reputation: 20158

Postgresql: create trigger to increment count_column between two tables?

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.

Upvotes: 0

Views: 1525

Answers (1)

drocha87
drocha87

Reputation: 629

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.

Upvotes: 2

Related Questions