SQL Server error 3609: Transaction ended in the trigger
A trigger ran ROLLBACK (or an error inside it rolled back), which undoes the whole transaction and aborts the rest of the batch.
- Error number
3609- Severity
- 16
- Category
- Transactions and locking
What it means
A trigger runs inside the transaction of the statement that fired it. If the trigger issues ROLLBACK TRANSACTION, everything in the transaction is undone, including work done before the triggering statement, and SQL Server aborts the batch with 3609. Triggers that validate data often do this on purpose; the 3609 just reports that the batch stopped.
Common causes
- A trigger that enforces a business rule with
ROLLBACKwhen the rule is broken. - An error inside the trigger that, with
XACT_ABORT(which is on by default inside triggers), rolls back the transaction. - Callers that don't expect a trigger on the table and assume their transaction is still open.
How to fix it
-
Read the message the trigger raised just before 3609; it usually explains the rule that was broken.
-
Prefer raising an error with
THROWin the trigger and letting the caller decide, or enforce the rule with aCHECKor foreign key constraint instead.CREATE OR ALTER TRIGGER dbo.trg_Orders_NoNegative ON dbo.Orders AFTER INSERT, UPDATE AS BEGIN IF EXISTS (SELECT 1 FROM inserted WHERE Quantity < 0) THROW 50001, 'Quantity cannot be negative.', 1; END; -
List triggers on a table to find out what's running.
SELECT name, is_disabled FROM sys.triggers WHERE parent_id = OBJECT_ID(N'dbo.Orders');
Related errors
3616Error raised during trigger execution266Transaction count mismatch after EXECUTE3902COMMIT has no corresponding BEGINORA-04088Oracle: error during execution of trigger
More transactions and locking errors
1205Deadlocked and chosen as the victim1222Lock request time out period exceeded3930Transaction cannot be committed3903ROLLBACK has no corresponding BEGIN3960Snapshot isolation update conflict6401No transaction or savepoint found3998Uncommittable transaction rolled back3621The statement has been terminated
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.