triggers.sql

-- Trigger Function: update_like_count
CREATE OR REPLACE FUNCTION update_like_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE posts
  SET like_count = (
    SELECT COUNT(*) FROM likes WHERE post_id = NEW.post_id
  )
  WHERE id = NEW.post_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger Function: update_comment_count
CREATE OR REPLACE FUNCTION update_comment_count()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE posts
  SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE post_id = NEW.post_id
  )
  WHERE id = NEW.post_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers on likes
CREATE TRIGGER trg_like_insert
AFTER INSERT ON likes
FOR EACH ROW EXECUTE FUNCTION update_like_count();

CREATE TRIGGER trg_like_delete
AFTER DELETE ON likes
FOR EACH ROW EXECUTE FUNCTION update_like_count();

-- Triggers on comments
CREATE TRIGGER trg_comment_insert
AFTER INSERT ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();

CREATE TRIGGER trg_comment_delete
AFTER DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION update_comment_count();