Trigger Warning for @GeneratedValue

How a simple database trigger can break @GeneratedValue in JPA / Hibernate.

After updating an application from Spring Boot 2 to 3, I ran into errors of the following type when trying to insert a new value into the database (Microsoft SQL Server):

java.lang.IllegalArgumentException: 
Can not set int field systems.enji.DemoEntity.id to null value

org.hibernate.AssertionFailure: null identifier (systems.enji.DemoEntity)
  at org.hibernate.engine.spi.EntityKey.<init>(EntityKey.java:52)

The referenced piece of code auto-generates an ID using a database identity column:

@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;

It turned out that the reason for the error was a database trigger that replaced the original insert with a slightly modified one. In fact, even a simple trigger such as the following (which only passes through the values of the original insert, without any additional logic) prevents Hibernate from retrieving the auto-generated value:

CREATE TRIGGER pass_through_trigger 
ON FOO_TABLE
INSTEAD OF INSERT
AS
INSERT INTO FOO_TABLE(COL1, COL2)
SELECT COL1, COL2 FROM INSERTED;

In Hibernate 5 (Spring Boot 2), this only meant that the ID was not immediately available in the managed entity, but the insert succeeded nonetheless. However, Hibernate 6 (Spring Boot 3) complains about this with one of the errors mentioned above.

To make sure that the auto-generated value is handed over to Hibernate, the trigger has to be extended with selecting SCOPE_IDENTITY():

CREATE TRIGGER pass_through_trigger 
ON FOO_TABLE
INSTEAD OF INSERT
AS
BEGIN
  INSERT INTO FOO_TABLE(COL1, COL2)
    SELECT COL1, COL2 FROM INSERTED;
  SELECT SCOPE_IDENTITY();
END;