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

Oracle APEX Tutorial: Master Database Development with Vinish.dev

As Rails developers, why we are excited about PostgreSQL 17

Cleaning up personal expense data with SQL

Preventing Out-of-Memory Crashes in MongoDB Sorting: Effective Optimization Strategies