无法在H2 DB中调用触发器 [英] Cannot call trigger in H2 DB

问题描述

我尝试运行一个简单的类从Java代码调用TRIGGER，我使用H2 DB，你能帮助我吗？这是我的简单代码（如H2中的示例代码）：

I tried run a simple class to call TRIGGER from Java code, I used H2 DB, can you help me? Here is my simple code (like sample code in H2):

public class TriggerTest {

public static void main(String[] args) throws Exception {
    System.out.println(TriggerTest.MyTrigger.class.getName());
    Class.forName("org.h2.Driver");
    Connection conn = DriverManager.getConnection("jdbc:h2:tcp://localhost/~/chap1", "sa", "sa");
    Statement stat = conn.createStatement();
    stat.execute("Drop Table if exists INVOICE");
    stat.execute("Drop Table if exists INVOICE_SUM");
    stat.execute("CREATE TABLE INVOICE(ID INT PRIMARY KEY, AMOUNT DECIMAL)");
    stat.execute("CREATE TABLE INVOICE_SUM(AMOUNT DECIMAL)");
    stat.execute("INSERT INTO INVOICE_SUM VALUES(0.0)");
    stat.execute("CREATE TRIGGER INV_INS AFTER INSERT ON INVOICE FOR EACH ROW CALL \""+TriggerTest.MyTrigger.class.getName()+"\"");
    stat.execute("CREATE TRIGGER INV_UPD AFTER UPDATE ON INVOICE FOR EACH ROW CALL \""+TriggerTest.MyTrigger.class.getName()+"\"");
    stat.execute("CREATE TRIGGER INV_DEL AFTER DELETE ON INVOICE FOR EACH ROW CALL  \""+TriggerTest.MyTrigger.class.getName()+"\"");

    stat.execute("INSERT INTO INVOICE VALUES(1, 10.0)");
    stat.execute("INSERT INTO INVOICE VALUES(2, 19.95)");
    stat.execute("UPDATE INVOICE SET AMOUNT=20.0 WHERE ID=2");
    stat.execute("DELETE FROM INVOICE WHERE ID=1");

    ResultSet rs;
    rs = stat.executeQuery("SELECT AMOUNT FROM INVOICE_SUM");
    rs.next();
    System.out.println("The sum is " + rs.getBigDecimal(1));
    conn.close();
}

public static class MyTrigger implements Trigger {

    public void init(Connection conn, String schemaName, String triggerName, String tableName, boolean before, int type) {
        // Initializing trigger
    }

    public void fire(Connection conn,
            Object[] oldRow, Object[] newRow)
            throws SQLException {
        BigDecimal diff = null;
        if (newRow != null) {
            diff = (BigDecimal) newRow[1];
        }
        if (oldRow != null) {
            BigDecimal m = (BigDecimal) oldRow[1];
            diff = diff == null ? m.negate() : diff.subtract(m);
        }
        PreparedStatement prep = conn.prepareStatement(
                "UPDATE INVOICE_SUM SET AMOUNT=AMOUNT+?");
        prep.setBigDecimal(1, diff);
        prep.execute();
    }

}

错误：
线程main中的异常org.h2.jdbc.JdbcSQLException：创建或初始化触发器INV_INS对象时出错，类vn.com.khangpn.core.trigger.TriggerTest $ MyTrigger，原因：org.h2.message。 DbException：Classvn.com.khangpn.core.trigger.TriggerTest $ MyTriggernot found [90086-169];详细了解根本原因; SQL语句：

And errors : Exception in thread "main" org.h2.jdbc.JdbcSQLException: Error creating or initializing trigger "INV_INS" object, class "vn.com.khangpn.core.trigger.TriggerTest$MyTrigger", cause: "org.h2.message.DbException: Class ""vn.com.khangpn.core.trigger.TriggerTest$MyTrigger"" not found [90086-169]"; see root cause for details; SQL statement:

推荐答案

在H2中，触发器类必须在数据库引擎的类路径中可用（使用服务器模式时，它必须位于服务器的类路径中）。

这篇关于无法在H2 DB中调用触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！