H2触发器（Triggers）行为进入无限循环

Question

我为H2数据库创建了一个触发器，用于自动更新一个时间戳列"updated_ts"。但是，当执行UPDATE SQL语句时，该触发器陷入了无限循环。

UPDATE main.doc doc 
SET value = 100
WHERE doc.id = 1;

触发器无限循环问题

@Override
public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
    
    log.debug("🔥🔥 触发了UpdateTimestamp表的触发器 {}",
            this.schemaName.concat(".").concat(this.tableName));
    
    if(Objects.isNull(newRow))
        return;
    
    final Long id = (Long)newRow[0];

    log.debug("🔥🔥 触发了id {} 的触发器", id);
    
    final LocalDate localDate = LocalDate.now();
    final LocalDateTime dateTime = LocalDateTime
            .of(localDate.getYear(), localDate.getMonthValue(), localDate.getDayOfMonth(), 0, 0, 0);
    
    final String update = new StringBuilder("UPDATE ")
            .append(this.schemaName).append(".")
            .append(this.tableName).append(" doc SET ")
            .append(" update_ts = ? ")
            .append(" where doc.id = ? ")
            .toString();
    
    final PreparedStatement statement = conn.prepareStatement(update);

    statement.setTimestamp(1, Timestamp.valueOf(dateTime));
    statement.setLong(2, id);
    
    final boolean ok = statement.execute();
    
    log.debug("Update status {} for UpdateTimestampShippingcalcDoc id {} table {}",
            ok , id, this.schemaName.concat(".").concat(this.tableName));
}

我认为这可能是由于H2数据库的行为导致的问题。

Ben Voigt · Answer

难怪会出现无限循环的情况：每当在您的表中执行写操作时，H2都会调用触发器处理程序。因此，如果您的代码在方法内部执行UPDATE操作，将会不断触发同一个处理程序。

不要在触发器处理程序代码中包含UPDATE操作：根据触发器规范，您可以在数据被写入之前通过修改newRow中的单元格来修改行值（即在更新之前直接修改新行数据）。

Micah · Answer

这里无需使用触发器，H2支持非标准的MySQL风格的ON UPDATE子句，并可以代替触发器来实现所需功能：

CREATE TABLE MAIN.DOC(
...
UPDATE_TS DATE
    DEFAULT CURRENT_DATE
    ON UPDATE CURRENT_DATE
    DEFAULT ON NULL
    NOT NULL,
...
);

DEFAULT表达式在插入新行且未为此列提供值时使用，而ON UPDATE表达式在更新行且未为此列提供新值时使用。DEFAULT ON NULL将传入的NULL值转换为默认值。

如果你确实需要使用触发器（例如，因为你的应用程序尝试设置UPDATE_TS列的不正确值，而且由于某些原因无法避免这种情况），那么该触发器只需在newRow数组中设置值即可。但在触发器中不要使用旧版日期时间API，在现代版本的H2中它们使用JSR-310数据类型。对于DATE列，请使用newRow[/*基于零的列索引*/] = LocalDate.now()。