Could not create trigger on MySQL

[k4jt]

`
-- +migrate Up

CREATE TABLE device_groups ...
CREATE TABLE devices ...

-- +migrate StatementBegin
delimiter |

CREATE TRIGGER update_devicegroup_devices_on_insert AFTER INSERT ON devices
FOR EACH ROW
BEGIN
UPDATE device_groups SET devices = (SELECT COUNT(*) FROM devices WHERE device_group_id = NEW.device_group_id) WHERE id = NEW.device_group_id;
END
|

delimiter ;
-- +migrate StatementEnd

-- +migrate Down
DROP TABLE IF EXISTS devices CASCADE;
DROP TABLE IF EXISTS device_groups CASCADE;
`

Using bindata

Error 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 'delimiter |

CREATE TRIGGER update_devicegroup_devices_on_insert AFTER INSERT ON' at line 2 handling 003_reinit.sql

[kjda]

same here!

[neoramos]

Hello,
Thank you for this great tool.
I was wondering if anyone has found a solution for migrating triggers.
This works from Sequel Pro IDE on a MySQL:

DROP TRIGGER IF EXISTS surveys.Survey_BEFORE_UPDATE;

DELIMITER $$
USE `surveys`$$
CREATE DEFINER = CURRENT_USER TRIGGER `surveys`.`Survey_BEFORE_UPDATE` BEFORE UPDATE ON `Survey` FOR EACH ROW
BEGIN
	IF NEW.status != OLD.status OR 
       NEW.surveyGroupID != OLD.surveyGroupID OR 
       NEW.surveyMonth != OLD.surveyMonth OR 
       NEW.surveyYear != OLD.surveyYear THEN
		INSERT INTO SurveyHistory (sequenceID,surveyGroupID,surveyYear,surveyMonth,`status`,statusAt,sentAt,lastActiveAt)
		VALUES (OLD.id,OLD.surveyGroupID,OLD.surveyYear,OLD.surveyMonth,OLD.`status`,OLD.statusAt,OLD.sentAt,OLD.lastActiveAt);
	END IF;
END$$
DELIMITER ;`

As reference for others searching how to migrate MySQL triggers and landing on this issue page.

DELIMITER is a command only understood by MySQL client. You need to get rid of it in order to have sql-migrate sucessfully deploy the migration scripts.

Procedure:

• First, make sure you have multiStatements enabled in your DSN. For example, in dbconfig.yml you would define a datasource like this: datasource: 'user:pass@tcp(db_host:db_port)/my_db?parseTime=true&multiStatements=true'.

• Second, remove all DELIMITER commands from your sql code. For example, code looking like this:

DELIMITER $$
USE `my_db`$$
CREATE DEFINER = CURRENT_USER TRIGGER `my_db`.`my_table_BEFORE_INSERT` 
BEFORE INSERT ON `my_table` 
FOR EACH ROW
BEGIN
	IF NEW.blah IS NULL THEN
		SET NEW.blah = '';
	END IF;
END$$

must be cleaned-up to look like this:

-- +migrate StatementBegin
USE `my_db`;
CREATE DEFINER = CURRENT_USER TRIGGER `my_db`.`my_table_BEFORE_INSERT` 
BEFORE INSERT ON `my_table` 
FOR EACH ROW
BEGIN
	IF NEW.blah IS NULL THEN
		SET NEW.blah = '';
	END IF;
END;
-- +migrate StatementEnd

Note that there is no DELIMITER $$ present and all occurrences of $$ were replaced with ;