Nov 16 2022 05:52 AM
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
Due to this exception, the previously executed batches (Batch 1, 2, 3, 4) are rolled back.
Commit -> On commit only the 10 inserts as part of Batch 5 are seen in the dbo.Employee and all others are lost.
Questions :