Home > Articles > Data

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account