Like this article? We recommend
Like this article? We recommend
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.