Error SQL syntax in a trigger, MySQL

Question

I receive this message "#1064 - 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 '' at line 6" but can not figure out what is wrong.

(position and points are MEDIUMINT, they are not primary key neither unique)

Anyone?

CREATE TRIGGER pointsAssigns
before INSERT ON MyTable
    FOR EACH ROW
BEGIN
IF NEW.position>6 THEN
    set NEW.points=5;
END IF;
END;

I tried both but does not work..The error should be in the '5'. — Stereo89
– Stereo89, Commented Jan 1, 2015 at 18:13

peterm · Accepted Answer · 2015-01-01 18:14:11Z

1

As @Mihai mentioned either add closing END and change the DELIMITER

DELIMITER //
CREATE TRIGGER pointsAssigns
BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
  IF NEW.position > 6 THEN
    SET NEW.points = 5;
  END IF;
END //
DELIMITER ;

Here is a SQLFiddle demo

or make it one-line trigger and then you don't need neither BEGIN...END block nor changing the DELIMITER

CREATE TRIGGER pointsAssigns
BEFORE INSERT ON MyTable
FOR EACH ROW
  SET NEW.points = IF(NEW.position > 6, 5, NEW.points);

Here is a SQLFiddle demo

answered Jan 1, 2015 at 18:14

peterm

93k16 gold badges157 silver badges162 bronze badges

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

1 Comment

Stereo89 Over a year ago

I have tried it before, but only NOW it seems to work..THNAK YOU

Collectives™ on Stack Overflow

Error SQL syntax in a trigger, MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related