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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.