-
Notifications
You must be signed in to change notification settings - Fork 275
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Could not create trigger on MySQL #71
Comments
same here! |
Hello,
|
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:
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 |
`
-- +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
The text was updated successfully, but these errors were encountered: