SQL return a Syntax error or access violation: 1064 during migration with symfony-CodePudding

I want to add a trigger that insert the new state of my ride entity into the ride_history entity:

DELIMITER //
CREATE TRIGGER after_updating_changes
AFTER UPDATE ON ride 
FOR EACH ROW
BEGIN
    IF OLD.status_id <> NEW.status_id THEN 
        INSERT INTO ride_history(`ride_id`, `status`, `created`) 
        VALUES(NEW.id, NEW.status_id, NOW());
    END IF;
END //
DELIMITER ;

I already tested it on phpmyadmin and there was no error during execution, but when I put it into my migration file and try to migrate the database, symfony returns this error:

An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL synt
  ax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DELIMITER // CREATE TRIGGER after_
  updating_changes AFTER UPDATE ON ride FOR E...' at line 1

So i'm a bit lost into all this, please help me!

CodePudding user response：

DELIMITER is client command, not SQL statement. So it causes an error while using as SQL statement.

Convert your trigger text to single-statement form making DELIMITER command unneeded:

CREATE TRIGGER after_updating_changes
AFTER UPDATE ON ride 
FOR EACH ROW
    INSERT INTO ride_history(`ride_id`, `status`, `created`) 
    SELECT NEW.id, NEW.status_id, NOW()
    WHERE OLD.status_id <> NEW.status_id;

PS. Take into account that if any status_id value is NULL then none will be saved into the history table. So you must adjust WHERE condition accordingly if this column is nullable.