CREATE OR REPLACE TRIGGER comments_bd
before DELETE ON comments FOR each ROW
BEGIN
  decrease_cc.before_d(:old.user_id);
END;
/
CREATE OR REPLACE TRIGGER comments_ad
after DELETE ON comments FOR each ROW
BEGIN
  decrease_cc.after_d;
END;
/
CREATE OR REPLACE PACKAGE decrease_cc AS
  PROCEDURE before_d (
    o_user_id comments.user_id%TYPE
  );
  PROCEDURE after_d;
END;
/
CREATE OR REPLACE PACKAGE BODY decrease_cc AS
  n_user_id comments.user_id%TYPE;

  PROCEDURE before_d (
    o_user_id comments.user_id%TYPE
  ) IS
  BEGIN
    n_user_id := o_user_id;
  END;

  PROCEDURE after_d IS
    comments_c  NUMBER;
  BEGIN
    SELECT COUNT(*) INTO comments_c FROM comments WHERE user_id = n_user_id;
    UPDATE users SET comments_count = comments_c WHERE id = n_user_id;
  END;
END;
/