mySQL TRIGGER error code 1064

Question

USE test;
CREATE TRIGGER AvgUpdateTrigger AFTER INSERT ON test.score
FOR EACH ROW
BEGIN
INSERT INTO test.average (test.average.TestID, test.average.TestAvg)
(SELECT test.score.TestID, avg(test.score.ScoreValue) FROM test.score GROUP BY test.score.TestID)
ON DUPLICATE KEY 
UPDATE test.average.TestAvg = (SELECT avg(test.score.ScoreValue) FROM test.score WHERE test.score.TestID = test.average.TestID GROUP BY test.score.TestID);
END;

im trying to update one table(average) when another one gets changed(score) it is telling me to add a semicolon but as you can see there is one there allready

What MySQL client are you using to execute this? It's possible you you need to specify an alternate `DELIMITER` http://stackoverflow.com/questions/10259504/delimiters-in-mysql — Michael Berkowski, Mar 22 '17 at 18:27
For Workbench, you probably do need to set `DELIMITER`. Follow the examples in the linked question. — Michael Berkowski, Mar 22 '17 at 18:42
For one-statement-triggers the delimiter doesn't need to be changed. Just remove the semicolon after your insert statement. And also remove BEGIN and END — Paul Spiegel, Mar 22 '17 at 19:00

score 0 · Accepted Answer · answered Mar 22 '17 at 19:11

If a trigger (or any stored procedure) contains only one statement, you don't need BEGIN and END:

CREATE TRIGGER AvgUpdateTrigger AFTER INSERT ON test.score
FOR EACH ROW
    INSERT INTO test.average (TestID, TestAvg)
        SELECT test.score.TestID, avg(test.score.ScoreValue)
        FROM test.score
        GROUP BY test.score.TestID
        ON DUPLICATE KEY UPDATE
            test.average.TestAvg = VALUES(TestAvg);

I also replaced the subquery with VALUES(TestAvg), since this value has already been selected.

mySQL TRIGGER error code 1064

1 Answers1