How to update another table using triggers in postgresql [closed]

Question

I'm using PostgreSQL.

I've 2 tables in my schema. One called taggings where I've (id, film_id, tag_id, user_id), all integers. And another where I intend to sum tags, called film_tag_counts (id, film_id, tag_id, count).

I'm trying to create a trigger for, when a new tagging is created or destroyed, the count in film_tag_counts get updated for the (film_id, tag_id).

I've created this trigger for the task:

  CREATE OR REPLACE FUNCTION update_film_tags_count()
  RETURNS TRIGGER
  AS
  $TRIGGER_update_film_tags_count$
    DECLARE
      film_tag_count_id integer;
      film_id_var integer;
      tag_id_var integer;
      count_var integer;
    BEGIN
      IF(TG_OP='INSERT') THEN
        SELECT NEW.film_id INTO film_id_var;
        SELECT NEW.tag_id INTO tag_id_var;
      ELSE
        SELECT OLD.film_id INTO film_id_var;
        SELECT OLD.tag_id INTO tag_id_var;
      END IF;

      SELECT id INTO film_tag_count_id FROM film_tag_counts WHERE tag_id = tag_id_var AND film_id = film_id_var;
      IF FOUND THEN
        SELECT count(*) INTO count_var FROM taggings WHERE tag_id = film_tag_count_id AND film_id = film_id_var;
        IF count_var = 0 THEN
          DELETE FROM film_tag_counts WHERE id = film_tag_count_id;
        ELSE
          UPDATE film_tag_counts SET count = count_var WHERE id = film_tag_count_id;
        END IF;
      ELSE
        INSERT INTO film_tag_counts (tag_id, film_id, count) VALUES (tag_id_var, film_id_var, (SELECT count(*) FROM taggings WHERE tag_id = tag_id_var AND film_id = film_id_var));
      END IF;
      RETURN null;
    END;
  $TRIGGER_update_film_tags_count$
  LANGUAGE plpgsql;

  CREATE TRIGGER taggings_update AFTER INSERT OR DELETE ON taggings FOR EACH ROW EXECUTE PROCEDURE update_film_tags_count();

But it doesn't seem to work properly. I've very little experience writing procedures at database side, and I'm not sure if this is a good approach. But I would like to test this. Could some one point me what I did wrong?

Thank you.

Answer 1

damn... it was a typo in the count query, the correct was:

SELECT count(*) INTO count_var FROM taggings WHERE tag_id = tag_id_var
AND film_id = film_id_var;

Sorry guys.