H2 Trigger doesn't Trigger properly

[Piotr]

I'm Trying to create an H2 Trigger which should trigger before an Insert query is executed. My Entity looks like this:

@Entity
@Table(name="Notes")
public class Notes {
@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE)
@Column(name="id")
private int id;
@Column(name="version")
private int version;
}

I've created trigger:

CREATE TRIGGER update_date Before INSERT ON notes FOR EACH ROW CALL 
TriggerBeforeInsert.class.getName()

I would like to set the version field to 1 whenever a new record is inserted. Here is the code for the Trigger:

public class TriggerBeforeUpdate implements Trigger {

@Override
public void init(Connection connection, String s, String s1, String s2, 
boolean b, int i) throws SQLException {

}

@Override
public void fire(Connection connection, Object[] oldRow, Object[] newRow) 
throws SQLException {
    newRow[1] = 1;
}

@Override
public void close() throws SQLException {

}

@Override
public void remove() throws SQLException {

}
}

And when I put a new record in the "notes" table, the trigger is called but unfortunately the version field does not change the value. It works only if I changes the id field and the version field like this:

@Override
public void fire(Connection connection, Object[] oldRow, Object[] newRow) 
throws SQLException {
    newRow[0] = 2 // any value differs from the current value
    newRow[1] = 1;
}

But this is not what I expects. What should I change to make the program work?

[Evgenij Ryazanov]

Hello.

On Sunday, 4 August 2019 23:48:40 UTC+8, Piotr wrote:

I've created trigger:

CREATE TRIGGER update_date Before INSERT ON notes FOR EACH ROW CALL 
TriggerBeforeInsert.class.getName()

I would like to set the version field to 1 whenever a new record is inserted. Here is the code for the Trigger:

public class TriggerBeforeUpdate implements Trigger {

TriggerBeforeInsert and TriggerBeforeUpdate are different classes.

[Piotr]

Yes, I made mistake when I was copying code. The class of course looks like this: public class TriggerBeforeInsert implements Trigger. The rest is unchanged. 

[Evgenij Ryazanov]

OK, let's test it with JDBC:

import java.sql.*;
import org.h2.api.Trigger;

public class TestTrigger {
    public static class T 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 {

            newRow[1] = 1;
        }

        @Override
        public void close() throws SQLException {}
        @Override
        public void remove() throws SQLException {}
    }

    public static void main(String[] args) throws Exception {
        try (Connection c = DriverManager.getConnection("jdbc:h2:mem:1")) {
            Statement s = c.createStatement();
            s.execute("CREATE TABLE NOTES(" //
                    + "ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, " //
                    + "VERSION INT)");
            s.execute("CREATE TRIGGER T1 BEFORE INSERT ON NOTES FOR EACH ROW CALL \"" + T.class.getName() + '"');
            s.executeUpdate("INSERT INTO NOTES() VALUES ()");
            s.executeUpdate("INSERT INTO NOTES(VERSION) VALUES (5)");
            c.setAutoCommit(false);
            s.executeUpdate("INSERT INTO NOTES() VALUES ()");
            s.executeUpdate("INSERT INTO NOTES(VERSION) VALUES (10)");
            c.commit();
            try (ResultSet rs = s.executeQuery("TABLE NOTES")) {
                while (rs.next()) {
                    System.out.printf("ID=%d, VERSION=%d%n", rs.getLong(1), rs.getInt(2));
                }
            }
        }
    }
}

With the latest version of H2 (1.4.199) it prints

ID=1, VERSION=1
ID=2, VERSION=1
ID=3, VERSION=1
ID=4, VERSION=1

So such trigger works as expected.

Because you're using some implementation of JPA your problem seems to be related with it and not with H2.