Lolechi Lolechi - 4 months ago 25
SQL Question

Mysql trigger math calculation

There are two tables: 'replies' and 'posts' and this trigger is placed on the 'replies' table.

Every time a new entry is placed in the replies table, the trigger checks if certain conditions are true for values in a row on the posts table with the matching ID as the new entry.

Here is what I have so far:

CREATE TABLE posts(
p_Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
p_Health INT UNSIGNED NOT NULL DEFAULT 0,
p_Bump INT UNSIGNED NOT NULL DEFAULT 0,
p_Time TIMESTAMP);

CREATE TABLE replies(
r_Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
r_To INT UNSIGNED NOT NULL,
r_Time TIMESTAMP);

CREATE TRIGGER bump AFTER INSERT ON replies
FOR EACH ROW
BEGIN
IF posts.p_Bump < 5 WHERE posts.p_Id = NEW.r_To AND (SELECT COUNT(r_To) FROM replies WHERE r_To = NEW.r_To)%10 = 10 THEN
UPDATE posts
SET posts.p_Bump = posts.p_Bump + 1 AND
SET posts.p_Health = 0 WHERE posts.p_Id = NEW.r_To;
END IF;
END;

INSERT INTO posts() VALUES();
INSERT INTO replies(r_To) VALUES(1);
INSERT INTO replies(r_To) VALUES(1);

INSERT INTO posts() VALUES();
INSERT INTO replies(r_To) VALUES(2);
INSERT INTO replies(r_To) VALUES(2);
INSERT INTO replies(r_To) VALUES(2);


'r_To' is shorthand for "reply to", it stores the ID of the post the reply is directed to.
SQLFiddle says there is an error on line 4, where I try to do an arithmetic operation with
SELECT COUNT(r_To)
by a modulo of 10.

Cannot create SQLFiddle, it doesn't save my schema if it's incorrect.

Answer

Two issues here! The first is that you need to change your delimiter to define your trigger.

DELIMITER $$
CREATE TRIGGER bump AFTER INSERT ON replies
FOR EACH ROW
BEGIN
  IF posts.p_Bump < 5 WHERE posts.p_Id = NEW.r_To AND (SELECT COUNT(r_To) FROM replies WHERE r_To = NEW.r_To)%10 = 10 THEN
    UPDATE posts 
      SET posts.p_Bump = posts.p_Bump + 1 AND
      SET posts.p_Health = 0 WHERE posts.p_Id = NEW.r_To;
  END IF;
END;

DELIMITER ;

The second is that delimiter command isn't supported on sqlfiddle.com so you will not be able to create a fiddle that way. I don't know the work around for sqlfiddle, but typing that into the console should do the trick.

Not quite sure if this is legit

IF posts.p_Bump < 5 WHERE posts.p_Id = NEW.r_To AND (SELECT COUNT(r_To) FROM replies WHERE r_To = NEW.r_To)%10 = 10 THEN

I think you will have to do

SELECT COUNT(r_To) into @myvar FROM replies WHERE r_To = NEW.r_To;
IF @myvar %10 = 10 THEN
   UPDATE posts 
      SET posts.p_Bump = posts.p_Bump + 1 AND
      SET posts.p_Health = 0 WHERE posts.p_Bump < 5 AND  posts.p_Id = NEW.r_To;
END IF;

This is a much simpler query and I think it does what you expect. However someint % 10 will never be 10 it can only take values of 0 to 9 so you will have to come up with the right conditions for that.

Comments