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,
);

score 6 · Answer 1 · answered Nov 04 '11 at 09:29

6

H2 doesn't support "ON UPDATE". But I guess you could use a computed column instead?:

CREATE TABLE IF NOT EXISTS TEST(
  DATE timestamp AS CURRENT_TIMESTAMP
);

answered Nov 04 '11 at 09:29

Thomas Mueller

48,905
14
116
132

@Thomas Mueller does this work on UPDATE or only on CREATE? – igr Apr 18 '16 at 18:29
2

This question is quite old, and H2 now supports "ON UPDATE". But if you ask for "IF EXISTS": this is only supported for "CREATE". – Thomas Mueller Apr 19 '16 at 09:22
4

I can't find anything in the docs about using on update. Could you provide a link? – uesports135 Jul 08 '17 at 17:47
1

Here's the git PR describing the change: https://github.com/h2database/h2database/pull/959 – AMilassin Dec 01 '18 at 15:03

Łukasz Gawron · Answer 2 · 2019-09-26T15:12:09.147

4

H2 supports "ON UPDATE" starting from version 1.4.197 (released 18 Mar 2018) Query from question should work just fine, as this one works in our project:

last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

I found that thanks to comments:

H2DB - script SQL ON UPDATE in CREATE TABLE request

So please upvote them as well.

Details:

edited Sep 26 '19 at 15:12

answered Mar 12 '19 at 08:27

Łukasz Gawron

897
10
20

1

Reference please? I can't find evidence of this searching current H2 docs – slashdottir Sep 24 '19 at 17:42
1

@slashdottir answer updated with some reference links. Please take a look. – Łukasz Gawron Sep 26 '19 at 15:12
I don't see this fix in 1.4.200 – Archmede Apr 30 '21 at 18:57

score 2 · Answer 3 · answered Feb 07 '18 at 01:52

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

H2DB - script SQL ON UPDATE in CREATE TABLE request

3 Answers3