Generated keys no longer returned if there is a trigger on insert

[OS-veracardoso]

Driver version

13.2.0.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM-CU21) (KB5025808) - 15.0.4316.3 (X64)
Jun 1 2023 16:32:31
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Client Operating System

• macOS Sequoia 15.6
• Ubuntu 24.04.2 LTS

.

JAVA/JVM version

21.0.7

Table schema

DDL:

CREATE TABLE TABLE_A (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY, NAME varchar(32));
CREATE TABLE TABLE_B (ID int NOT NULL IDENTITY(1,1) PRIMARY KEY);
CREATE TRIGGER INSERT_ON_TABLE_B ON TABLE_A FOR INSERT AS INSERT INTO TABLE_B DEFAULT VALUES;

Problem description

There is a table (TABLE_A) with an auto-generated primary key and a trigger on insert that inserts a single row in another table (TABLE_B).

When inserting a single row in table TABLE_A it would be to expect the new generated key to be returned, however, an exception is thrown when calling getResultSet: com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained..

Code snipet:

            String sql =  "INSERT INTO TABLE_A (NAME) VALUES (?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[]{"ID"});
            preparedStatement.setString(1, "test");
            boolean execute = preparedStatement.execute();
            ResultSet generatedKeys = preparedStatement.getGeneratedKeys();
            int updateCount = preparedStatement.getUpdateCount();

Expected behavior

When executing a single insert in TABLE_A the generated primary key is returned (🔴).
The update count is returned as 1 (✅ ).

Actual behavior

The update count is returned as 1, as expected, but the generated primary key is not returned.

Error message/stack trace

com.microsoft.sqlserver.jdbc.SQLServerException: The statement must be executed before any results can be obtained.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:250)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(SQLServerStatement.java:2394)

Any other details that can be helpful

• When using driver version 13.1.1.jre11-preview, this works as expected.
• If we remove the trigger, it works as expected.

JDBC trace logs

Provide the JDBC driver trace logs. Instructions can be found here: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

[OS-veracardoso]

Hi @Ananya2 I just tested the release from today and it I just realized that the update row count is not correct when the statement inserts multiple values. For instance,
INSERT INTO TABLE_A (NAME) VALUES (?), (?) should return 2 as update row count, but it returns 1. For the scenarios where there is no trigger the result count is correct. Could you please take a look? Please let me know if you need additional info.

[Ananya2]

Hi @OS-veracardoso - Thanks for pointing this out. I just tried to reproduce the behavior using a simple test case with a table having an AFTER INSERT trigger and a multi-value INSERT statement, but the update count seemed correct on my end.
Could you please share a minimal reproduction where you’re seeing the incorrect update count?

[OS-veracardoso]

Hi @OS-veracardoso - Thanks for pointing this out. I just tried to reproduce the behavior using a simple test case with a table having an AFTER INSERT trigger and a multi-value INSERT statement, but the update count seemed correct on my end. Could you please share a minimal reproduction where you’re seeing the incorrect update count?

Hi @Ananya2

Appologies, I should have been more clear.

So, if I run

            String sql = "INSERT INTO TABLE_A(NAME) VALUES (?), (?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1");
            preparedStatement.setString(2, "2");
            boolean execute = preparedStatement.execute();
            if (!execute) {
                int updateCount = preparedStatement.getUpdateCount();
                System.out.println(updateCount);
            }

The output is 1 whereas it should 2.
Using the version 13.1.1.jre11-preview of the driver, the output is 2 as expected, since 2 new rows are added.

However, if I run

            String sql = "INSERT INTO TABLE_A(NAME) VALUES (?), (?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "1");
            preparedStatement.setString(2, "2");
            System.out.println(preparedStatement.executeUpdate());            

then yes, the result is 2.

What I'm trying to say is that the preparedStatement.getUpdateCount() in the fist example should also retunr 2.

[Ananya2]

Created this PR to address the issue: #2817