Database triggers not working

Here's the SQL query for the function which get the post_id whenever there is a new entry in the table voters and based on that it count all the posts having same ID and set that value in the post table in a column.

Function

CREATE OR REPLACE FUNCTION update_vote_count()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
  UPDATE post SET votes = (SELECT COUNT(*) FROM voters WHERE post_id = NEW.post_id) WHERE post_id = NEW.post_id;
  RETURN NEW;
END;
$BODY$

Trigger

CREATE TRIGGER voters_count
  AFTER UPDATE
  ON voters
  FOR EACH ROW
  EXECUTE PROCEDURE update_vote_count();

The problem is that, this function is not triggering and I don't even know how to debug these database functions/triggers.

Comments

[Dian Fay]

1. Set up logging and see if any errors show up.
2. It's also possible that the trigger is executing, but not having the result you expect. You could create a debugging table with a single text field and have your trigger function insert a record into it, which would narrow the problem down to your update query construction. There's also LISTEN/NOTIFY if you can't or don't want to create more tables.

[Yashu Mittal]

It's also possible that the trigger is executing, but not having the result you expect. You could create a debugging table with a single text field and have your trigger function insert a record into it, which would narrow the problem down to your update query construction.

Dam! That's a really good suggestion, why didn't I thought of that even though we use console.log() statements inside the codebase a lot. 😅

---

Also I will also look into logging and LISTEN/NOTIFY.