Databases Reference
In-Depth Information
NOTE
Trigger s and stored procedures described here are more challenging with the
code-first
approach to development of the entity model. The database management functionality
built into the Entity Framework does not support triggers and stored procedures
directly, requiring you to manage their source code separately and making database
deployment more complex. The
database-first
and
model-first
approaches, on the other
hand, rely on SQL scripts for database deployment and do not have this problem.
Implementing Business Rules in Stored Procedures
Similar to validation rules, it is possible to optimize business rule methods by re-
implementing them in SQL stored procedures. Listing 9.5 shows a stored procedure, which
is a T-SQL equivalent of the
FulfillOrder
business rule method implemented earlier.
LISTING 9.5
FulfillOrder Stored Procedure
CREATE PROCEDURE [dbo].[FulfillOrder]
@orderId int
AS
BEGIN
UPDATE Orders SET
OrderStatus = 3,
ShippedDate = GETDATE()
WHERE OrderID = @orderId
AND OrderStatus = 2;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR ('Order status must be Paid for the order to be fulfilled', 11, 0);
RETURN;
END;
UPDATE Products SET
UnitsInStock = UnitsInStock - od.Quantity
FROM Products AS p
JOIN [Order Details] AS od ON p.ProductID = od.ProductID
WHERE od.OrderID = @orderId;
END
The T-SQL version of the
FulfillOrder
is slightly more efficient as it checks the current
order status as part of the
UPDATE
statement that changes it. The
@@ROWCOUNT
system vari-
able indicates the actual number of rows affected by the previous statement, allowing this
code to determine if the
UPDATE
statement was successful. Note that unlike throwing an
