SQL Error (1064) when Creating a Trigger in MySQL

“SQL Error (1064): You have an error in your SQL syntax” occurs when a query has been incorrectly delimited. This is a common cause of confusion when creating triggers as the internal statement needs to be terminated in addition to the create or alter trigger statement. MySQL uses a semi-colon as the default delimiter and does not work with nested delimiters, e.g.

CREATE TRIGGER ... AFTER INSERT ON ...
FOR EACH ROW
BEGIN
    INSERT ...;
END;

Notice how there are two semi-colon delimiters in the above example. We need to temporarily define a different delimiter to workaround this problem, the following example uses a double forward slash:

DELIMITER //;

Now we need to add the new delimiter at the end of our statement so that it looks like this:

CREATE TRIGGER ... AFTER INSERT ON ...
FOR EACH ROW
BEGIN
    INSERT ...;
END;//

Assuming that there are no other syntax errors, the statement should execute without any errors. To restore the delimiter to a semi-colon use the following query:

DELIMITER ;//

Included below is a full example with the necessary table schema:
Continue reading