Dumping triggers with semicolon separated statements

[MalteWunsch]

When slimdumping a table with triggers that contain semicolon separated statements, these semicolons are not escaped. slimdump would output something like this:

-- BEGIN TRIGGERS mytable
CREATE TRIGGER `mytrigger`
    AFTER INSERT ON `mytable`
    FOR EACH ROW
    BEGIN
        DECLARE myvariable INT;
        SET myvariable = (SELECT COUNT(*) FROM othertable);
        IF (myvariable = 0) THEN
            ...;
        END IF;
    END;

Result is a syntax error.

The usual solution for that is to set a custom delimiter. E.g. using mysqldump gives something like this:

DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`test`@`localhost`*/ /*!50003 TRIGGER `mytrigger`
    AFTER INSERT ON `mytable`
    FOR EACH ROW
    BEGIN
        DECLARE myvariable INT;
        SET myvariable = (SELECT COUNT(*) FROM othertable);
        IF (myvariable = 0) THEN
            ...;
        END IF;
    END */;;
DELIMITER ;

(I'm not sure what the comments are about)

[relthyg]

The comments make sure that the statements in them are executed at a proper MySQL Version.

e.g. /*!40101 SET NAMES utf8 */ tells MySQL to execute the SET NAMES... statement on MySQL 4.1.1 and later only. Triggers came in 5.0.3, so the corresponding statements cannot be executed in older versions.