Play framework 2.0 evolutions and create trigger

Question

I have been trying to get this trigger created using Play 2.0.3. It runs perfectly fine in MySQL if I run it manually, but it fails when trying to run it from Play.

delimiter |
    create trigger company_updated before update on company
        for each row begin
            set new.updated = CURRENT_TIMESTAMP;
        end;
    |
delimiter ;

The error it throws is this:

We got the following error: 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 company_updated before update on company for each row' at line 1 [ERROR:1064, SQLSTATE:42000], while trying to run this SQL script:

Answer 1

This used to be a bug, and has been fixed in 2.1. You can use double semicolons to escape. It's documented here: http://www.playframework.com/documentation/2.1.0/Evolutions

So your code should be changed as below:

create trigger company_updated before update on company
    for each row begin
        set new.updated = CURRENT_TIMESTAMP;;
end;

Answer 2

The delimiter keyword is not a SQL statement, it is only used in the mysql command line client.

Try to remove the delimiter parts:

create trigger company_updated before update on company
    for each row begin
        set new.updated = CURRENT_TIMESTAMP;
    end;

But as you mentioned, you may hit a Play bug with the semi-colon :(