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

Question

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

In MySQL, this can be done easily:

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

Answer 1

Declare this trigger:

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

Implementing a trigger using 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 a trigger using jOOQ:

Since you added the jOOQ tag to the question, I suspect that this alternative may also be relevant. Of course, you can use jOOQ inside the H2 trigger:

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