DEV Community

Yashu Mittal
Yashu Mittal

Posted on

Database triggers not working

#database #postgres #help #question

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.

Top comments (2)

Collapse
 
dmfay profile image
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.
Collapse
 
mittalyashu profile image
Yashu Mittal • • Edited

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.