CODEWITHSUNDEEP
mysql
Lolechi
Lolechi

Reputation: 151

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.

Upvotes: 1

Views: 2192

Answers (2)

e4c5
e4c5

Reputation: 53734

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

I think the modulo should be done inside the subselect 

BEGIN
  IF posts.p_Bump < 5 
       WHERE posts.p_Id = NEW.r_To 
       AND (SELECT COUNT(r_To)%10 
              FROM replies WHERE r_To = NEW.r_To) = 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;

Upvotes: 1

Related Questions