Home > Articles > Data

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

 

  • + Share This
  • 🔖 Save To Your Account