CODEWITHSUNDEEP
mysqlsqltriggers
Ant
Ant

Reputation: 15

MySQL syntax error in Trigger IF statement?

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; //

Upvotes: 1

Views: 90

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562358

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

Upvotes: 1

Related Questions