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)

Dian Fay • Feb 12 '20

Set up logging and see if any errors show up.
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 • Feb 12 '20 • 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.

DEV Community

Database triggers not working

Top comments (2)

Read next

Database Sharding: Simplifying Data Scalability

Unlocking DuckDB from Anywhere - A Guide to Remote Access with Apache Arrow and Flight RPC (gRPC)

How I Went From Being a DBA to DevOps Engineer

How to Crack Your First DevOps Interview: Tips and Sample Questions