What will happen if a trigger on a table hits the part of the trigger that includes the RAISERROR code below?

RAISERROR('Attempt to modify supposedly immutable number.',16,1)

How will we know this code was executed?


The errors generated by RAISERROR operate the same as errors generated by the Database Engine code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

If you are in trigger context, all errors terminate the batch and roll back the transaction on the spot.

When it comes to error handling in SQL Server, no rule is valid without an exception. Errors you raise yourself with RAISERROR do not abort the batch, not even in trigger context

There is one more way that a trigger can terminate the batch. This happens if @@trancount is 0 when the trigger exits. A trigger always executes in the context of a transaction, since even if there is no multi-statement transaction in progress each INSERT, UPDATE and DELETE statement is its own transaction in SQL Server, and the trigger is part of that transaction. Doc

  • 5
    I think it is worth noting that a RAISERROR with severity 16 outside the TRY block in the trigger will not rollback the trigger transaction; the error will be returned to the client but the statement that fired the trigger will succeed unless there is other code in the trigger to rollback the transaction.
    – Dan Guzman
    Oct 22 '15 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.