0

I have a forum site running and want to update the number of comments added to each post. This MUST be done by trigger and i was thinking of having it like this: A comment is submitted and the trigger then takes the id of the original post and updates the column comments in the table Post where the id is the same, by increasing the number already in there. If that makes sense?

CREATE TRIGGER addcomment
AFTER INSERT ON Reply FOR EACH ROW
BEGIN
  DECLARE v_postid integer;
  SELECT post_id() INTO v_postid FROM dual;
  UPDATE Post
    SET comments = comments +1
    WHERE post_id=v_postid;
END;
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
SlyTech404
  • 198
  • 1
  • 11
  • How is this attempt failing? It looks like it should work, assuming there's a function `post_id()` defined. – Michael Berkowski Apr 09 '15 at 14:28
  • there is no function post_id() that would be the column name – SlyTech404 Apr 09 '15 at 14:29
  • that's not how you access the data from the triggering row... all you'd really need is `update post ... where post_id = NEW.postid`. `new is a pseudo table/record representing the row that fired the trigger, e.g. your new comment. – Marc B Apr 09 '15 at 14:31
  • This is the error I'm getting: CREATE TRIGGER addcomment AFTER INSERT ON Reply FOR EACH ROW BEGIN DECLARE v_postid INTEGER; MySQL sa: Dokumentasjon #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 – SlyTech404 Apr 09 '15 at 14:35
  • What MySQL client are you using? You may need to specify a delimiter first http://stackoverflow.com/questions/10259504/delimiters-in-mysql/10259528#10259528 – Michael Berkowski Apr 09 '15 at 15:28

1 Answers1

0

I fixed it! Somehow:

CREATE TRIGGER addcomment
AFTER INSERT ON Reply FOR EACH ROW
BEGIN
 UPDATE Post
   SET comments = comments +1
   WHERE post_id=NEW.post_id;
END
SlyTech404
  • 198
  • 1
  • 11