- 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
Product Catalog: Table Insertions, Changes, and Deletions
Assuming that we have a small catalog that will fit onto a single page, the modification date and time of the product catalog page will take the following information into account:
- Date and time of last modification to the Categories table
- Date and time of last modification to the Products table
The major challenge in calculating the modification date and time for a database table is the absence of any date/time timestamps in most SQL databases. (The Lotus Notes database, for example, attaches complete modification history to each database object.) Fortunately, a similar functionality can be achieved with SQL triggers. To simulate table-wide timestamps, we’ll define a new Timestamps table and two stored SQL procedures: SetTimestamp to set the timestamp, and GetTimestamp to read it (see Listing 2).
Listing 2 Timestamps table and associated procedures.
CREATE TABLE Timestamps( TSName varchar(50) NOT NULL PRIMARY KEY, TSDate datetime NOT NULL DEFAULT GETDATE()) CREATE PROCEDURE SetTimestamp @TSName varchar(50) AS BEGIN UPDATE Timestamps SET TSDate = GETDATE() WHERE TSName = @TSName IF @@ROWCOUNT = 0 INSERT Timestamps (TSName,TSDate) VALUES (@TSName,GETDATE()) END CREATE PROCEDURE GetTimestamp @Name varchar(50),@Timestamp datetime OUTPUT AS BEGIN SET @Timestamp = (SELECT MAX(TSDate) FROM Timestamps WHERE TSName = @Name) IF @Timestamp IS NULL BEGIN EXECUTE SetTimestamp @Name SET @Timestamp = (SELECT TSDate FROM Timestamps WHERE TSName = @Name) END SELECT @Timestamp AS LastModified END
With this new tool, we can define triggers for the Categories and the Products tables that will set the timestamps when these tables are modified (see Listing 3). By modifying data in the Categories or Products tables, you can easily verify that the timestamps are actually set.
Listing 3 Triggers for tables related to product catalogs.
CREATE TRIGGER CategoriesModified ON Categories AFTER INSERT,UPDATE,DELETE AS EXECUTE SetTimestamp ’Categories’ CREATE TRIGGER ProductsModified on Products AFTER INSERT,UPDATE,DELETE AS EXECUTE SetTimestamp ’Products’
Last but not least, we’ll define the ProductCatalogModified stored procedure to abstract the database details from the web developers (see Listing 4).
Listing 4 Stored procedure to compute product catalog modification date/time.
CREATE PROCEDURE ProductCatalogModified AS BEGIN DECLARE @modCategories datetime DECLARE @modProducts datetime EXECUTE GetTimestamp ’Categories’,@modCategories EXECUTE GetTimestamp ’Products’,@modProducts IF @modProducts < @modCategories SET @modProducts = @modCategories SELECT @modProducts AS LastModified END
The stored procedure compares two timestamps relevant to the product catalog (the Categories timestamp and the Products timestamp) and returns the later one as a recordset that can be used by the web pages.
Web developers can now get the product catalog modification date and time with a simple SQL statement (VBScript code using ActiveX data objects is shown in Listing 5) and combine it with other modification timestamps (for example, page-design modification timestamp).
Listing 5 VBScript code to retrieve catalog modification date/time.
Set adoRS = adoConnection.Execute ("EXECUTE ProductCatalogModified")
LastModified = Max(LastModified,adoRS("LastModified").value)
If your product catalog is public and doesn’t change based on who’s looking at it (some companies show different prices or different ranges of products to different users), you can omit the ETag header and mark the content as publicly cacheable. The content-expiration time (the Expires header) would be based on the frequency of expected catalog change. For example, if you change products every weekend, the Expires header could be set to next Sunday midnight.