Postgres trigger to update counter in another table not working

• There is users table and places table.

• The users table has column id(primary key), username, place_count.

• The places table has column id(primary key), name, user_id (user_id foreign key)

• Each user can post multiple photos and so I want the column"place_count" to keep the count of user-specific places from places table (eg, user with id 1 having place_count = 150, user with id 2 having place_count = 244 etc etc).

I made use of trigger

Trigger function

CREATE FUNCTION log_place_count_update_to_user()  RETURNS TRIGGER as $$  BEGIN      IF (TG_OP = `INSERT`) THEN          UPDATE users           SET place_count = place_count + 1          WHERE user_id;      ELSEIF (TG_OP = `DELETE`) THEN          UPDATE users          SET place_count = place_count - 1           WHERE id = NEW.user_id AND place_count > 0;      END IF;      RETURN NEW;  END  $$  LANGUAGE PLPGSQL;  

Trigger creation

CREATE TRIGGER log_place_count_update      AFTER UPDATE OR DELETE      ON places      FOR EACH ROW      EXECUTE PROCEDURE log_place_count_update_to_user();  

I have inserted some users in users table with initial value of place_count = 0.

PROBLEM : When I update the places with the name and user_id I expect the trigger to increment the place_count by 1 in users table. But no increment is happening in place_count of user table.
What am I doing wrong?

https://stackoverflow.com/questions/66728319/postgres-trigger-to-update-counter-in-another-table-not-working March 21, 2021 at 10:31AM