mysql 5.5 触发器,在mysql5.5上通过JDBC创建触发器时出错

This is my code :

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());

And this is the error thrown :

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.

解决方案

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.

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.