H2 Triggers behaving in infinite loop

Question

I create a trigger for H2 database to automate the update of a timestamp column "updated_ts". That trigger behaves as an infinite loop when UPDATE sql statment is called.

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

Trigger infinite looping

@Override
    public void fire(Connection conn, Object[] oldRow, Object[] newRow) throws SQLException {
        
        log.debug("🔥🔥 FIRED Trigger for UpdateTimestamp table {}",
                this.schemaName.concat(".").concat(this.tableName));
        
        if(Objects.isNull(newRow))
            return;
        
        final Long id = (Long)newRow[0];

        log.debug("🔥🔥 FIRED Trigger for id {}", id);
        
        final LocalDate localDate = LocalDate.now();
        final LocalDateTime dateTime = LocalDateTime
                .of(localDate.getDayOfYear(), localDate.getMonth(), 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));
    }

I think it's of H2 behavor

Answer 1

You must write your trigger with AFTER SQL statement

CREATE TRIGGER update_timestamp_trigger
    AFTER UPDATE
    ON public.bill
    FOR EACH ROW
    CALL "loop.update.trigger.UpdateTimestamp";

I was using BEFORE