H2DB - script SQL ON UPDATE in CREATE TABLE request

Question

How to do an ON UPDATE in a CREATE TABLE request in H2 database.

Context:

I'm using the sql-maven-plugin (1.5) to generate a table in an h2 database in my project.

But when I call the script sql, I have a org.h2.message.DbException.getJdbcSQLException.

My script:

CREATE TABLE IF NOT EXISTS TEST(
  DATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);

Answer 1

Add to your SQL file:

CREATE TABLE IF NOT EXISTS my_table (
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TRIGGER my_trigger
  BEFORE UPDATE
  ON my_table
  FOR EACH ROW CALL "org.h2.trigger.UpdatedAtTrigger";

Create a Java class and add to your test classpath:

package org.h2.trigger;

import java.sql.*;  
import java.time.Instant;
import org.h2.tools.TriggerAdapter;

public class UpdatedAtTrigger extends TriggerAdapter {

  @Override
  public void fire(Connection conn, ResultSet oldRow, ResultSet newRow) throws SQLException {
    newRow.updateTimestamp("updated_at", Timestamp.from(Instant.now()));
  }
}