Databases Reference
In-Depth Information
IF @productName <> null
BEGIN
DECLARE @errorMessage NVARCHAR(MAX) = @productName + ' is out of
stock';
RAISERROR (@errorMessage, 11, 0);
END;
END;
Just like the last version of the Order validation logic that was implemented using LINQ in
the application code, this T-SQL code executes a single query, joining the Order Details
and the Products tables to find order items for products out of stock. The only notable
difference in the query logic is the use of the
inserted
pseudo-table, which in this trigger
provides access to the rows of the Orders table that were inserted or updated. The query
sets the
@productName
variable with the name of an out-of-stock product, and the trigger
calls the
RAISERROR
system function if any such products are found.
The first parameter of
RAISERROR
is the error message that will be reported to the applica-
tion. The second parameter,
severity
, indicates the type of error. Severity values between 11
and 16 indicate user errors and throw a
SqlException
in .NET applications. The third
parameter,
state
, provides additional information about where the error has occurred,
and you can simply use zero, if you don't plan to use it.
Custom validation errors implemented this way can be handled similarly to the built-in
errors, such as the
UNIQUE KEY
and
FOREIGN KEY
constraint violations discussed earlier. All
that is needed is to change the
HandleSqlException
method of the
NorthwindEntities
class, as shown next, to handle the
SqlException
instances thrown by
RAISERROR
:
private static void HandleSqlException(Exception e)
{
SqlException error = e.InnerException as SqlException;
if (error != null)
{
if (error.Number == 547)
HandleReferenceConstraintViolation(error);
else if (error.Number == 2627)
HandleUniqueKeyConstraintViolation(error);
else if (error.Number >= 50000 && error.Class <= 16)
throw new ValidationException(error.Message);
}
}
The
HandleSqlException
method wraps all
SqlException
instances with error codes 50000
and above and severity levels 16 and below in
ValidationException
instances. These error
codes and severity levels distinguish the
user errors
reported by calling
RAISERROR
from
informational messages
and
system errors
. Informational messages have severity level 10 or
lower and do not throw a
SqlException
. System errors have severity level 17 or higher,
and although they also throw
SqlException
, system errors are not expected during normal
execution of the application and thus should not be reported as validation errors.
