I'm very new to SQL and am having issues with syntax in my triggers. I am trying to set a minimum rating of 1 and a maximum rating of 5 for horses after a race. The problem seems to be with the IF statement. Any help would be greatly appreciated.
CREATE TRIGGER new_rating
AFTER insert on stats
FOR EACH ROW
BEGIN
IF (new.rating > 5) THEN
SET new.rating = 5
ELSEIF (new.rating < 1) THEN
SET new.rating = 1
END IF;
END; //
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
This is the error. It means you can't use SET to change any of the values of the row you are inserting, because in an AFTER INSERT trigger, the row has already been inserted. You would have to do this in a BEFORE INSERT trigger for this to work.
You should read https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html which says:
Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.
If I correct this and change it to a BEFORE INSERT trigger, I get this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF (new.rating < 1) THEN ...
I notice you did not end the SET statement with a semicolon. This is necessary to terminate every statement in the trigger, including SET statements.
Here's the trigger that works:
CREATE TRIGGER new_rating BEFORE insert on stats
FOR EACH ROW BEGIN
IF (new.rating > 5) THEN
SET new.rating = 5;
ELSEIF (new.rating < 1) THEN
SET new.rating = 1;
END IF;
END
