Syntax exception on trigger with multiple statemen

问题:

I am trying to create a trigger that performs multiple operations in MySQL 5.5.28 with InnoDB.

I have two tables, "test" and "test_watcher": changes to the first are recorded in the watcher table with the help of triggers. The last trigger needs to perform 2 operations on DELETE, it works in MySQL Workbench (with DELIMITER) but doesn't if I create it with JDBC.

CREATE TRIGGER `AD_test_FER` AFTER DELETE
  ON `test`
  FOR EACH ROW
      BEGIN
        -- if it's been inserted, modified and deleted but never synced, 
        -- the revision is NULL: no one needs to know about it
        DELETE FROM test_watcher WHERE pk = OLD.id AND revision IS NULL;

        -- if it has been synced already, we just update the flag
        UPDATE test_watcher SET flag = -1 WHERE pk = OLD.id;
      END;

I keep getting com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax.

I know it works with DELIMITER $$ in Workbench, but JDBC doesn't support it.

I've achieved it in PostgreSQL and would post the code if necessary.

回答1:

This behavior might be caused by the connection property allowMultiQueries=true. My guess is this property will make MySQL break up queries on the ; as a query separator and then execute those as separate queries, essentially breaking your trigger creation code.

As you said in a - now deleted - answer that adding allowMultiQueries=true actually solved the problem (contrary to my expectiation), the problem might actually be the last ; in your query. So another thing to check is if the problem goes away by removing the last ; (in END;) in your script (and not using allowMultiQueries=true). Some database don't consider ; to be valid at the end of a statement (as it is actually a delimiter to separate statements).

(this answer is based on my comment above)

回答2:

If JDBC does not support delimiters (DELIMITER is a client command), then execute these two statements separately - one by one.