Forum Discussion
Application encounters SQLServerException while there is an exception in batch executing
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 :
- 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 ?
- 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 ?