Unexpected END_OF_INPUT in MySQL trigger

Question

I have searched for all the possible online solutions but I can't figure out the error in this trigger.

    CREATE TRIGGER `delete_neat_link`
    AFTER DELETE ON `neat_urls`
    FOR EACH ROW 
    BEGIN
        DELETE FROM `css_paths` 
            WHERE `css_paths`.`path_id` = OLD.`neat_link`;
    END;

the first error appears at OLD.neat_link

    syntax error, unexpected END_OF_INPUT, expecting ';'

and the second one at END;

    syntax error, unexpected END

Any help would be appreciable, thanks.

Answer 1

That problem is due to interpreting individual statements. The CREATE TRIGGER statement is as such a single complete statement that must be sent as is to the server. Usually statement borders are recognized by the default delimiter (the semicolon). In case of stored programs however the semicolon is needed to separate inner statements. This would confuse the client as it cannot tell apart what is an inner statement of the stored program or a full statement as it must be sent as a whole to the server.

Hence the DELIMITER statement was introduced which only applies to clients (not the server, the server itself cannot parse this statement). It changes the default delimiter to one of your choice, leading so the client where to look for the statement's end. A typical case hence looks like this:

DELIMITER ;;
CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END;;