- The Sandbox
- Product Catalog: Table Insertions, Changes, and Deletions
- Product Description: Row-Modification Timestamp
- List Orders: Modifying Related Tables
- Show Order: Joined Tables
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
List Orders: Modifying Related Tables
The caching considerations for the page displaying a list of all orders for the current user depend heavily on its content. If we assume that we want to display total order value and number of items, together with the information from the order header, we need to track modifications to the OrderItems table as well as modifications to the Orders table.
Furthermore, modifications made by one user shouldn’t affect validity of cached pages of other users, so we need to track modifications to the Orders table on a per-user basis. (Each user would get his or her own timestamp in the Timestamps table.) Since we also have to track per-row changes (we’ll need these timestamps when displaying individual orders), the per-table trigger will only fire on deletions. The trigger code could get quite complex, involving SQL cursors to step through the list of deleted records, but we can avoid such complexity with a clever trick—delete all relevant timestamps and re-create them with DELETE and INSERT statements (see Listing 8).
Listing 8 Order-deletion trigger.
CREATE TRIGGER OrderModified ON Orders AFTER DELETE AS BEGIN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION DELETE FROM Timestamps WHERE TSName IN (SELECT ’Orders_’+CAST(UserID AS VARCHAR) FROM DELETED) INSERT Timestamps(TSname) SELECT DISTINCT ’Orders_’+CAST(UserID AS VARCHAR) FROM DELETED COMMIT TRANSACTION END
To detect the row modifications, we add the LastModified column to the Orders table and an UPDATE trigger that changes it whenever a row in the Orders table is modified (see Listing 9).
Listing 9 LastModified column added to the Orders table.
ALTER TABLE Orders ADD LastModified datetime DEFAULT GETDATE() CREATE TRIGGER OrderRowModified ON Orders AFTER UPDATE AS BEGIN SET NOCOUNT ON UPDATE Orders SET LastModified = GETDATE() FROM Orders INNER JOIN Inserted ON (Orders.OrderID = Inserted.OrderID) END
The last step in the database preparation is a trigger that changes the LastModified column in the Orders table whenever there’s a change (INSERT, UPDATE, or DELETE) in the related rows in the OrderItems table. To simplify our code, we’ll define two triggers—one that handles inserts and updates (this one will refer to the inserted logical table) and another one that handles deletes (using the deleted logical table), as shown in Listing 10.
Listing 10 Order item triggers.
CREATE TRIGGER OrderItemInserted ON OrderItems AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON UPDATE Orders SET LastModified = GETDATE() FROM Orders INNER JOIN Inserted ON (Orders.OrderID = Inserted.[Order]) END CREATE TRIGGER OrderItemDeleted ON OrderItems AFTER DELETE AS BEGIN SET NOCOUNT ON UPDATE Orders SET LastModified = GETDATE() FROM Orders INNER JOIN Deleted ON (Orders.OrderID = Deleted.[Order]) END
The triggers on the OrderItems table complete the database changes needed to track the modifications to order headers or individual items in the orders. As a convenience to the web programmers, we might also create a stored procedure that will return the latest modification date/time for all orders created by the specified user ID (see Listing 11).
Listing 11 Compute the latest modified date for all customers’ orders.
CREATE PROCEDURE OrdersModified @UserID uniqueidentifier AS BEGIN DECLARE @modTimestamp datetime DECLARE @modRowValues datetime DECLARE @TSName varchar SET @TSName = ’Orders_’+CAST(@UserID AS Varchar) EXECUTE GetTimestamp @TSName,@modTimestamp SET @modRowValues = (SELECT Max(LastModified) FROM Orders Where UserID = @UserID) IF @modTimestamp < @modRowValues SET @modTimestamp = @modRowValues /* ISNULL below handles the case where @modTimestamp is NULL and thus not comparable to @modRowValues in the IF statement above. */ SELECT ISNULL(@modTimestamp,@modRowValues) AS LastModified END
This procedure reads the value of the per-user timestamp (tracking order deletions) and combines it with the latest order-modification date/time, returning the result as a recordset to the dynamic web page, where it can be used as shown earlier in Listing 5.