jdbc 创建mysql触发器,带有MySQL和JDBC的多个语句的触发器上的语法异常

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.

解决方案

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)