Postgres trigger to update counter in another table not working

Question

• 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 INSERT 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?

Answer 1

To answer your question: multiple syntax errors. This should work:

CREATE FUNCTION log_place_count_update_to_user()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
BEGIN
   CASE TG_OP
   WHEN 'INSERT' THEN            -- single quotes
      UPDATE users AS u
      SET    place_count = u.place_count + 1
      WHERE  u.id = NEW.user_id;  -- fixed
   WHEN 'DELETE' THEN
      UPDATE users AS u
      SET    place_count = u.place_count - 1 
      WHERE  u.id = OLD.user_id
      AND    u.place_count > 0;
   ELSE
      RAISE EXCEPTION 'Unexpected TG_OP: "%". Should not occur!', TG_OP;
   END CASE;
   
   RETURN NULL;      -- for AFTER trigger this can be NULL
END
$func$;

Your original would actually produce error messages.

And your trigger needs to address INSERT instead of UPDATE:

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

(UPDATE is not covered, yet!)

But (as has been commented) I wouldn't go there. Keeping count with a trigger is expensive and error prone.

With an index on places (user_id) (like you should have anyway) this query returns the current count very quickly:

SELECT count(*) FROM places WHERE user_id = $user_id;