as shown below, I use jdbc to create a MySQL trigger, which is successfully created in navicat. After the sql2 is output, it runs successfully in navicat. However, when the program runs, it will prompt that there is a syntax error in the statement that created the trigger. Ask for advice.
Code:
Connection conn = JDBCDao.getConnection();
String sql2 = "DROP TRIGGER IF EXISTS tb_fieldInsert; "
+"CREATE TRIGGER tb_fieldInsert "
+"BEFORE INSERT ON tb_table_fields "
+"FOR EACH ROW "
+"BEGIN "
+"DECLARE aInsert VARCHAR(200); "
+"SET aInsert = new.name; "
+"INSERT INTO tb_modify_history (tb_id,field,content,time) VALUES (1,aInsert,CONCAT("\"",aInsert,"\""),now()); "
+"END; ";
Statement state;
//PreparedStatement ps;
try {
//
state = conn.createStatement();
state.executeUpdate(sql2);
...
}catch (Exception e) {
e.printStackTrace();
} finally {
JDBCDao.closeConnection(conn);
}
error report:
com.mysql.jdbc.exceptions.jdbc4.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 "CREATE TRIGGER tb_fieldInsert BEFORE INSERT ON tb_table_fields FOR EACH ROW" at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)