在 mysql5.5 上通过 JDBC 创建触发器时出错 [英] Error while creating trigger through JDBC on mysql5.5

问题描述

这是我的代码:

triggerBuilder.append("DROP TRIGGER IF EXISTS `insert_associated_inquiry`; ");
triggerBuilder.append(" DELIMITER %% ");

triggerBuilder.append(" CREATE TRIGGER insert_associated_inquiry BEFORE UPDATE ON inquiry ");
triggerBuilder.append(" FOR EACH ROW Begin ");    

triggerBuilder.append(" insert into associated_inquiries(inquiry_id , subject , content , inquiry_date , preferred_date ) " );
triggerBuilder.append("values");
        triggerBuilder.append(" ( " );
            triggerBuilder.append(" OLD.id , ");
            triggerBuilder.append(" OLD.subject , " );
            triggerBuilder.append(" OLD.content , " );
            triggerBuilder.append(" OLD.created_on , " );
            triggerBuilder.append(" OLD.preffered_date " );
        triggerBuilder.append(" ) ; ");

triggerBuilder.append(" END %% ");

triggerBuilder.append(" DELIMITER ; ");

con.createStatement().execute(triggerBuilder.toString());

这是抛出的错误:

 com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 insert_associated_inquiry 
 BEFORE UPDATE ON inquiry ' at line 1

此错误的原因和解决方法是什么.请帮忙.谢谢.

What could be the reason and solution for this error. Help please.Thanks.

推荐答案

不要在 JDBC 和 MySQL 中使用分隔符.分隔符仅由 MySQL 控制台使用，以便它可以判断您输入的触发器、存储过程等何时结束.在 JDBC 中，您将整个 SQL 字符串放在一起，然后将其发送到数据库.由于您可以控制何时将 SQL 发送到数据库，因此无需使用分隔符.

Don't use delimiters with JDBC and MySQL. Delimiters are only used by the MySQL console so that it can tell when a trigger, stored procedure, etc that you're typing in has ended. In JDBC, you put the whole SQL string together and then send it to the database. Because you're in control of when the SQL gets sent to the database, there's no need to use delimiters.

我从您的代码中删除了两行 DELIMITER 和 %% 分隔符的使用，并将 DROP TRIGGER 命令发送到数据库分开.我留下的代码如下:

I removed the two DELIMITER lines and the use of the %% delimiter from your code, and sent the DROP TRIGGER command to the database separately. The code I was left with is as follows:

con.createStatement().execute("DROP TRIGGER IF EXISTS `insert_associated_inquiry`");

triggerBuilder.append(" CREATE TRIGGER insert_associated_inquiry BEFORE UPDATE ON inquiry ");
triggerBuilder.append(" FOR EACH ROW Begin ");    

triggerBuilder.append(" insert into associated_inquiries(inquiry_id , subject , content , inquiry_date , preferred_date ) " );
triggerBuilder.append("values");
        triggerBuilder.append(" ( " );
            triggerBuilder.append(" OLD.id , ");
            triggerBuilder.append(" OLD.subject , " );
            triggerBuilder.append(" OLD.content , " );
            triggerBuilder.append(" OLD.created_on , " );
            triggerBuilder.append(" OLD.preffered_date " );
        triggerBuilder.append(" ) ; ");

triggerBuilder.append(" END ");

con.createStatement().execute(triggerBuilder.toString());

此代码似乎有效，因为无论触发器是否已存在，我都可以毫无错误地运行此代码.如果触发器以前不存在，则会创建它.

This code appeared to work, in that I could run this code without error regardless of whether the trigger already existed. If the trigger did not previously exist it was created.

这篇关于在 mysql5.5 上通过 JDBC 创建触发器时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！