Home > Articles > Data

  • Print
  • + Share This
Like this article? We recommend

Show Order: Joined Tables

It’s time to do the last web page in our example—the page that displays an individual order. Good news first: We already have all the fields and triggers we need to detect when the last database change relevant to a single order occurred. Now for some bad news: Because the product names and descriptions affect the HTML content shown to the end user, we have to consider changes to products referenced from the OrderItems table when computing the LastModified date/time.

The necessary code (yet again in an SQL stored procedure to increase performance) is shown in Listing 12.

Listing 12 Compute the last-modified date for an order.

CREATE PROCEDURE OrderModifiedDate
 @OrderID int
AS BEGIN
 DECLARE @modOrder datetime
 DECLARE @modProduct datetime
 SET @modOrder = (SELECT LastModified FROM Orders
  WHERE OrderID = @OrderID)
 SET @modProduct = (SELECT MAX(Products.LastModified)
  FROM Products INNER JOIN OrderItems
   ON (Products.ProductID = OrderItems.Product)
  WHERE OrderItems.[Order] = @OrderID)
 IF @modOrder < @modProduct SET @modOrder = @modProduct
 SELECT @modOrder AS LastModified
END

The stored procedure fetches the modification date/time of the order header. (This field also reflects changes made to individual lines in the order due to the OrderItemInserted and OrderItemDeleted triggers created in Listing 10.) It then finds the maximum LastModified value of all products referred by the OrderItems rows and returns the maximum of both values.

  • + Share This
  • 🔖 Save To Your Account