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);
CREATE TRIGGER [dbo].[AutoGenerateOMAInsertTrigger]
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)
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.
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 ?