How to make trigger in MYSQL that acts as counter when I insert new comment on my post

user_13063134 0
Viewed 81

I would like to know how to update column numberOfComments to display number of comments that are made on the post. Can I create a trigger on table comments and update column numberOfComments in table posts? Tables look something like this.

create table posts (
   post_id int primary key,
   post_subject varchar(255),
   numberOfComments int);

create table comments(
   comment_id int primary key,
   comment_text,
   post_id int,
   foreign key (post_id) references posts(post_id));
database-trigger mysql
3 Answers

Generally it's best to have your queries generate these kinds of numbers rather than rig up a trigger. You could do it with something like this

  SELECT p.post_id, p.post_subject, COALESCE(c.ccount) numberOfComments
   FROM posts p
   LEFT JOIN (
                SELECT post_id, COUNT(*) ccount
                  FROM comments
                 GROUP BY post_id
              ) c ON p.post_id = c.post_id;

SQL is made for this kind of query; it has surprisingly good performance, especially with the primary keys you use. You can read up on loose index scans if you want to know more about why performance is good.

You could create a view in your database that gives the same results as the table you propose, like this

CREATE VIEW post_with_comment_count AS
  SELECT p.post_id, p.post_subject, COALESCE(c.ccount) numberOfComments
   FROM posts p
   LEFT JOIN (
                SELECT post_id, COUNT(*) ccount
                  FROM comments
                 GROUP BY post_id
              ) c ON p.post_id = c.post_id;

Why is this a good way to solve your problem? It's easier to read and maintain than a trigger, less prone to failure, and declarative rather than procedural.

If you get to the point where the performance of this approach is poor, you can do things a different way. But you won't get there until you have many millions of posts and comments. Until then, keep it simple.

user_2056080

I agree with other answers and comments that it's tricky to use triggers to keep the number of comments updated perfectly. You would think this would be reliable, but in practice it isn't.

But to answer your question, here's a demo:

I created tables as you show in your question, then add a row to posts:

insert into posts set post_id = 1, post_subject = 'subject';

Here's the trigger:

create trigger updNumberOfComments after insert on comments 
for each row 
  update posts set numberOfComments = COALESCE(numberOfComments, 0) + 1 
  where post_id = NEW.post_id;

This trigger is simply one statement so I was able to write it without a BEGIN...END block. But if you do anything that requires a block, then you should read about using DELIMITER.

Then add a few comments:

insert into comments set comment_id = 1, comment_text = 'comment 1', post_id = 1;
insert into comments set comment_id = 2, comment_text = 'comment 2', post_id = 1;
insert into comments set comment_id = 3, comment_text = 'comment 3', post_id = 1;

Confirm that it incremented the number of comments:

select * from posts;
+---------+--------------+------------------+
| post_id | post_subject | numberOfComments |
+---------+--------------+------------------+
|       1 | subject      |                3 |
+---------+--------------+------------------+
Bill_Karwin_208600

you can use mysql triggers, unlike select or join query, they will persist in your database until they are dropped.

DROP TRIGGER IF EXISTS `comment_count_incrementer`;

DELIMITER $$

CREATE TRIGGER `comment_count_incrementer`AFTER INSERT ON comments 
  FOR EACH ROW 
    BEGIN
      UPDATE posts 
      SET numberofComments = CASE numberOfComments
                               WHEN IS NULL THEN 1
                               ELSE numberofComments + 1
                             END
      WHERE posts.post_id = comments.post_id;
    END$$

DELIMITER ;

above is a gist of how triggers are implemented

user_75510920
Related