“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:

Table Creation

CREATE TABLE `search_index` (
	`search_index_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`table_name` VARCHAR(50) NOT NULL,
	`entity_id` INT(10) UNSIGNED NOT NULL,
	`search_text` TEXT NOT NULL,
	PRIMARY KEY (`search_index_id`),
	UNIQUE INDEX `table_name_entity_id` (`table_name`, `entity_id`),
	FULLTEXT INDEX `search_text` (`search_text`)
) ENGINE=MyISAM;

CREATE TABLE `address` (
	`address_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`line1` VARCHAR(50) NOT NULL,
	`line2` VARCHAR(50) NULL DEFAULT NULL,
	`town` VARCHAR(50) NULL DEFAULT NULL,
	`city` VARCHAR(50) NULL DEFAULT NULL,
	`postcode` VARCHAR(10) NOT NULL,
	PRIMARY KEY (`address_id`)
) ENGINE=InnoDB;

Trigger Creation

DELIMITER //;

CREATE TRIGGER `after_insert_address` AFTER INSERT ON `address`
FOR EACH ROW
BEGIN
    INSERT search_index
        (
            table_name,
            entity_id,
            search_text
        )
    VALUES
        (
            'address',
            NEW.address_id,
            CONCAT_WS
                (
                    ' ', 
                    NEW.line1,
                    NEW.line2,
                    NEW.town,
                    NEW.city,
                    NEW.postcode
                )
        );
END;//

DELIMITER ;//