msg 3609 the transaction ended in the trigger

In this tutorial, we will discuss how to fix the error, msg 3609 the transaction ended in the trigger the batch has been aborted.

Recently, while working with SQL Server transactions with trigger, I got the below error.

“the transaction ended in the trigger the batch has been aborted“

Here is what I was doing.

• Consider the following two tables:
  • Employees: Contains employees’ information
  • Department: Contains department information

• We will create a trigger on the Employees table that will run after any insert statement executed on the table.
• The trigger will check if the department ID mentioned in the inserted record is available in the department table or not i.e. the department ID is valid or not.
• We will begin the insert transaction explicitly before the Insert statement.
• If the department ID inserted is valid, the transaction will be committed. Otherwise, the transaction will be rolled back.

USE [master]
GO
CREATE TRIGGER [dbo].[TrigEmployees]
ON [dbo].[Employees]
AFTER INSERT
AS
BEGIN
	DECLARE
		@DepID int
	SELECT @DepID= INSERTED.DepID FROM INSERTED
	IF NOT EXISTS(SELECT * FROM dbo.Department WHERE DepID= @DepID)
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION
END

• The code looks fine and logical.
• But if we try to insert a row with a valid department ID, we will get the same error.

BEGIN TRANSACTION
INSERT INTO dbo.Employees(EmpID, EmpName,DepID)
VALUES(1011, 'Johnny', 12)
COMMIT TRANSACTION

But when I was executing, I was getting the below error.

msg 3609 the transaction ended in the trigger – Solution

This is basically a message to state that the transaction has been rolled back within a SQL server trigger. When you write a rollback statement inside a trigger, you are likely to face this message.

• Here it was coming because the transaction is rolled back. The correct way to write this logic is to write the COMMIT statement outside the trigger.

USE [master]
GO

ALTER TRIGGER [dbo].[TrigEmployees]
ON [dbo].[Employees]
AFTER INSERT
AS
BEGIN
	DECLARE
		@DepID int
	SELECT @DepID= INSERTED.DepID FROM INSERTED
	IF NOT EXISTS(SELECT * FROM dbo.Department WHERE DepID= @DepID)
		ROLLBACK TRANSACTION
END

• Now if we insert the same row, we will not get the same message. Instead the row will be successfully inserted into the table and the transaction will be committed.

BEGIN TRANSACTION
INSERT INTO dbo.Employees(EmpID, EmpName,DepID)
VALUES(1011, 'Johnny', 12)
COMMIT TRANSACTION

• But if we try to insert a row with an invalid department ID, we will get the same message i.e. ‘The transaction ended in the trigger. The batch has been aborted.‘, which is the expected result i.e. the transaction is rolled back.

You may like the following SQL server tutorials:

• SQL Server stored procedure if else
• How to view stored procedure in SQL Server
• String or binary data would be truncated in SQL Server
• Create Foreign Key in SQL Server – Complete tutorial
• Saving changes is not permitted in SQL Server
• The conversion of a date data type to a DateTime data type resulted in an out-of-range value SQL

This is how to fix the error msg 3609 the transaction ended in the trigger the batch has been aborted which comes while working with triggers and transactions.