geeeeeeeek geeeeeeeek - 7 months ago 23
SQL Question

simple mysql trigger, cannot find the syntax error. appreciate for it

I am using phpmyadmin and got a syntax error from my trigger part. I followed examples online but didn't see anything wrong. Does anyone see the syntax error? Thank you in advance.

Here is my code:

CREATE TRIGGER INSERT_T BEFORE INSERT ON dataT
for each row begin
IF(NEW.id IS NOT NULL)
SET NEW.id = 9999;
END IF
end;


mysql reports that:

#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 'SET NEW.id = 9999' at line 4

.

Answer

Missing THEN keyword.

MySQL Reference Manual: IF Syntax https://dev.mysql.com/doc/refman/5.7/en/if.html

And for your next questions. Yes, you need a semicolon following the END IF

And you need to specify a delimiter other than the default semicolon. e.g.

DELIMITER $$

CREATE TRIGGER ...

END$$

DELIMITER ;