// SQL Server error reference

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
Error message
The transaction ended in the trigger. The batch has been aborted.

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 ROLLBACK when 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

  1. Read the message the trigger raised just before 3609; it usually explains the rule that was broken.

  2. Prefer raising an error with THROW in the trigger and letting the caller decide, or enforce the rule with a CHECK or 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;
  3. 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

More transactions and locking errors

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.

// fewer errors, faster fixes

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.