mysql不支持语句级触发器,mysql jdbc驱动程序不支持具有多个语句的触发器中的分隔符...

i've got some code that is triggering a syntax error because of some misplaced semicolons. if this was running on the command line, i'd solve this with a delimiter. unfortunately, the jdbc4 driver doesn't seem to recognize delimiters. anyway to get this to run?

delimiter |

CREATE TRIGGER obs_update BEFORE UPDATE ON obs

FOR EACH ROW

BEGIN

IF OLD.voided = 0 AND NEW.voided = 1 THEN

DELETE FROM clinic_obs WHERE id = OLD.obs_id;

ELSE

UPDATE clinic_obs SET clinic_obs.revision_token = NOW()

WHERE NEW.obs_id = clinic_obs.id;

END IF;

END;

|

delimiter ;

解决方案

Delimiter is a command for SQL client. There is no need to use delimiter in JDBC.

Example below shows it:

import java.sql.*;

public class TriggerExample {

public static void main(String args[]) {

String connectionURL = "jdbc:mysql://localhost:3306/test";

Connection con = null;

try {

Class.forName("com.mysql.jdbc.Driver");

con = DriverManager.getConnection(connectionURL, "login",

"password");

Statement stmt = con.createStatement();

stmt.execute("CREATE TRIGGER obs_update BEFORE UPDATE ON obs "

+ "FOR EACH ROW "

+ "BEGIN "

+ "IF OLD.voided = 0 AND NEW.voided = 1 THEN "

+ " DELETE FROM clinic_obs WHERE id = OLD.obs_id; "

+ "ELSE "

+ " UPDATE clinic_obs SET clinic_obs.revision_token = NOW() "

+ " WHERE NEW.id = clinic_obs.id; "

+ "END IF; "

+ "END;");

con.close();

} catch (SQLException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

} finally {

if (con != null) {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

}