MySQL Trigger Error on IF

Question

Here is my SQL, but the MySQL server consistently says that it has an error in the SQL syntax. I have tried many times, but it doesn't work. Please Help!

DELIMITER //
CREATE TRIGGER Score_Count_for_Contestant
AFTER INSERT ON award
FOR EACH ROW
BEGIN
 IF NEW.award = 'gold'AND contestant.CID=new.CID THEN
  UPDATE contestant SET contestant.score= contestant.score+10

IF new.Award='silver' AND contestant.CID=new.CID THEN
   UPDATE contestant SET contestant.score= contestant.score+8

IF new.Award='bronze' AND contestant.CID=new.CID THEN
   UPDATE contestant SET contestant.score= contestant.score+6
 END IF
END //

DELIMITER ;

Missing ; delimiters after the end of your UPDATE queries. — jso
– jso, Commented Dec 20, 2015 at 15:39

Gordon Linoff · Accepted Answer · 2015-12-20 15:53:30Z

1

I would eliminate the if and simplify the logic:

UPDATE contestant c
    SET c.score = (case when NEW.award = 'gold' then c.score + 10
                        when NEW.award = 'silver' then c.score + 8
                        when NEW.award = 'bronze' then c.score + 6
                        else c.score
                   end)
    WHERE c.CID = new.CID

You can add this condition to the where clause NEW.award IN ('gold', 'silver', 'bronze') if there are lots of other awards.

Your code doesn't work because you are referencing contestant.CID in the IF, and it is not defined. Note: you could still have an issue with the delimiter, but that is unclear from the code sample.

answered Dec 20, 2015 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jso Over a year ago

Didn't notice the contestant reference, removing my original answer then (as fixing the delimiters would not solve the problem). Thanks for pointing it out.

mobility Over a year ago

Thank you very much Gordon Linoff! I love you!!! It works! And you make up my mind

Collectives™ on Stack Overflow

MySQL Trigger Error on IF

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related