1

For reasons unknown to me, the control flow doesnt reach "ITS CORRECT BUT PRIZE IS MONETARY" block, that corresponds to variable bounty not being NULL. I'm pretty confident that SELECT Bounty FROM Questions WHERE QuestionID = NEW.QuestionID must return INT UNSIGNED.

So, is my syntax wrong? Am I using some kind of bad IF condition?

The trigger code: pastebin

CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`    
FOR EACH ROW    
BEGIN    
DECLARE bounty INT UNSIGNED;  
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` = 
 NEW.`QuestionID`);  
 IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0 
THEN     // IF ITS CORRECT
  IF bounty IS NULL THEN    // FIELD `Bounty` being NULL means that a prize is set.
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE
      BEGIN  
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY              
             `PrizeID` LIMIT 1;  
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);  
      END;  
    ELSE    //IF NOT LEAVE A MESSAGE!
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );  
    END IF;  
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN  // DO WE HAVE ENOUGH BUDGET TO PAY?
      BEGIN  
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;  
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;  
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, CAST(bounty AS CHAR(50)), NOW(), FALSE);  
      END;  
    ELSE   // NO MORE MONEY, I'M AFRAID
     INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY", 
 NOW(), FALSE );  
   END IF;  
  END IF;  
END IF;  
END;

Updated my code like so due to Jacob H's suggestion. No results.

  CREATE TRIGGER `CheckCorrect` AFTER INSERT ON `UserAnswers`    //CLAIM PRIZE AND NOTIFY!
FOR EACH ROW    
BEGIN    
DECLARE bounty INT UNSIGNED;   
SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`);   
IF (SELECT `IsCorrect` FROM `Answers` WHERE `AnswerID` = NEW.`AnswerID`)<>0 THEN     // IF ITS CORRECT
   BEGIN   
  IF bounty IS NULL THEN    // IF ITS A PRIZE CLAIM IT
    BEGIN  
    IF EXISTS (SELECT * FROM `Prizes` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL) THEN    //ANY SPARE PRIZES?
      BEGIN   
      UPDATE `Prizes` SET `ClaimerID` = NEW.`UserID` WHERE `QuestionID` = NEW.`QuestionID` AND `ClaimerID` IS NULL ORDER BY `PrizeID` LIMIT 1;   
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NEWPRIZE", NOW(), FALSE);   
      END;   
    ELSE    //IF NOT LEAVE A MESSAGE!
      BEGIN   
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOPRIZE", NOW(), FALSE );   
      END;   
    END IF;   
    END;  
  ELSE     // ITS CORRECT BUT PRIZE IS MONETARY
    BEGIN   
    IF (SELECT `Budget` FROM `Questions` WHERE `QuestionID`=NEW.`QuestionID`)>bounty THEN   
      BEGIN   
      UPDATE `Questions` SET `Budget`=`Budget`-bounty WHERE `QuestionID` = NEW.`QuestionID`;   
      UPDATE `Users` SET `Balance`=`Balance`+bounty WHERE `UserID`=NEW.`UserID`;   
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`,CAST(bounty AS CHAR(50)), NOW(), FALSE);   
      END;   
    ELSE   
      BEGIN  
      INSERT INTO `Notifications` VALUES (NULL, NEW.`UserID`, "NOMONEY", NOW(), FALSE );   
      END;  
    END IF;   
    END;   
  END IF;   
  END   
END IF;   
END; 
  • 1
    Can you nest an IF/THEN in MySQL without BEGIN/END? Maybe it's short-circuiting to "IF NOT LEAVE A MESSAGE!"? Have you tried reading the bounty value at execution time? – Jacob H Jul 20 '18 at 16:07
  • To clarify: You've got entries in Notifications where the third column's value is "NEWPRIZE" and "NOPRIZE", but none where the value is "NOMONEY" or a monetary value? And, the trigger is not generating any errors? ON general principles, you might want to explicitly cast bounty from an integer to a character value when you go to set that third column to that value. I'm not necessarily thinking that's the problem, but it's a good idea in principle. – RDFozz Jul 20 '18 at 16:14
  • @RDFozz Notification table contains only "NOPRIZE" messages for occasions that were supposed to spawn INT or "NOMONEY" results. So I concluded that control flow does not reach this block at all. Yeah, I can try casting bounty but not doing it did not raise any errors. Will correct that now. – Torlan Delta Jul 20 '18 at 16:17
  • @JacobH Umm.. I will try and remove BEGIN/END; as well as one instruction. Maybe it will work, but I don't see how it can help. Also, I'm not sure how can I ping bounty value during runtime. Will google how to do that. – Torlan Delta Jul 20 '18 at 16:20
  • No I mean ADD it. Not remove it. In your nested IF/THEN statement. Sorry that was a bit confusing. – Jacob H Jul 20 '18 at 16:22
0

All I did was change

SET bounty:=(SELECT `Bounty` FROM `Questions` WHERE `QuestionID` = 
NEW.`QuestionID`); 

to

SELECT `Bounty` INTO bountyvar FROM `Questions` WHERE `QuestionID` = NEW.`QuestionID`; 

and it started working... Why though xD

Some light can be shed on that from this question: apparently some versions of MySQL just don't like some variations of syntax. Oh well. I'm glad this is over. I'm tired.

  • I didn't know just beating around a bush like that could yield any results or I would have done it sooner. Hell. – Torlan Delta Jul 22 '18 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.