Home > Articles > Data

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.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.