java H2 - 如何创建将行更改记录到另一个表的数据库触发器?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/29993308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-11-02 16:15:20  来源:igfitidea点击:

H2 - How to create a database trigger that log a row change to another table?

javasqltriggersh2jooq

提问by Pinch

How to create a database trigger that log a row change to another table in H2?

如何创建一个数据库触发器,将行更改记录到 H2 中的另一个表?

In MySQL, this can be done easily:

在 MySQL 中,这可以轻松完成:

CREATE TRIGGER `trigger` BEFORE UPDATE ON `table`
  FOR EACH ROW BEGIN
    INSERT INTO `log`
    (
      `field1`
      `field2`,
      ...
    )
    VALUES
    (
      NEW.`field1`,
      NEW.`field2`,
      ...
    ) ;
    END;

回答by Lukas Eder

Declare this trigger:

声明这个触发器:

CREATE TRIGGER my_trigger
BEFORE UPDATE
ON my_table
FOR EACH ROW
CALL "com.example.MyTrigger"

Implementing the trigger with Java/JDBC:

使用 Java/JDBC 实现触发器:

public class MyTrigger implements Trigger {

    @Override
    public void init(Connection conn, String schemaName, 
                     String triggerName, String tableName, boolean before, int type)
    throws SQLException {}

    @Override
    public void fire(Connection conn, Object[] oldRow, Object[] newRow)
    throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
            "INSERT INTO log (field1, field2, ...) " +
            "VALUES (?, ?, ...)")
        ) {
            stmt.setObject(1, newRow[0]);
            stmt.setObject(2, newRow[1]);
            ...

            stmt.executeUpdate();
        }
    }

    @Override
    public void close() throws SQLException {}

    @Override
    public void remove() throws SQLException {}
}

Implementing the trigger with jOOQ:

使用 jOOQ 实现触发器:

Since you added the jOOQ tag to the question, I suspect this alternative might be relevant, too. You can of course use jOOQ inside of an H2 trigger:

由于您在问题中添加了 jOOQ 标签,我怀疑这个替代方案也可能相关。您当然可以在 H2 触发器内使用 jOOQ:

    @Override
    public void fire(Connection conn, Object[] oldRow, Object[] newRow)
    throws SQLException {
        DSL.using(conn)
           .insertInto(LOG, LOG.FIELD1, LOG.FIELD2, ...)
           .values(LOG.FIELD1.getDataType().convert(newRow[0]), 
                   LOG.FIELD2.getDataType().convert(newRow[1]), ...)
           .execute();
    }