Application encounters SQLServerException while there is an exception in batch executing

Occasional Visitor

I am using SQLServer 2017.  I have created an application in Java 8. I am using SQL Server JDBC driver (mssql-jdbc-7.2.2.jre8.jar) downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=57782

 

DDL 

create table dbo.Employee (id int identity(1,1) primary key, name varchar(100), age int);

create table dbo.Employee_Audit (id int primary key, name varchar(100), age int);

 

Trigger  - 

 

CREATE TRIGGER [dbo].[AutoGenerateOMAInsertTrigger] 
   ON  [dbo].[Employee]
   AFTER INSERT
AS 
BEGIN
    DECLARE @threshold int = 0
    SET @threshold = (select max(id) from dbo.Employee t)
    if @threshold = 20
        insert into dbo.Employee_Audit (id, name, age) values (12,'abc', 23)
END

 

 

With the DDL & trigger provided as above,  let us consider the following example

 

Table Name : dbo.Employee 

Table Name to which the Trigger inserts : dbo.Employee_Audit

  • Batch 1 : 10 INSERTS -> Executed successfully on dbo.Employee -> Trigger is fired and inserts are successfully executed on dbo.Employee_Audit
  • Batch 2 : 10 UPDATES -> Executed successfully on dbo.Employee
  • Batch 3 : 10 UPDATES -> Executed successfully on dbo.Employee
  • Batch 4 : 5 INSERTS -> Executed on dbo.Employee -> Trigger is fired and causes a Primary Key violation on dbo.Employee_Audit. This throws a SQLServerException which is caught by the Java application communicating with dbo.Employee.

Due to this exception, the previously executed batches (Batch 1, 2, 3, 4) are rolled back.

 

  • Batch 5 : 10 INSERTS -> Executed successfully on dbo.Employee -> Trigger is fired and inserts are successfully executed on dbo.Employee_Audit

Commit -> On commit only the 10 inserts as part of Batch 5 are seen in the dbo.Employee  and all others are lost.

 

Questions : 

 

  1. Is this the expected behaviour of SQLServer ? How can I rolled back only batch 4 ? Is there any way to achieve it in SQL Server ?
  2. If a primary key violation is hit on table "dbo.Employee" (which is the table communicated with the Java application) an exception of type BatchUpdateException is seen. But in the above mentioned scenario SQLServerException is hit rather than a BatchUpdateException. Why is this behaviour different ?

 

0 Replies