- 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 Description: Row-Modification Timestamp
After all the effort needed to figure out when the whole product catalog was last modified, you might be tempted to take a shortcut and decide that all product descriptions were modified at the same time as the catalog. If your product catalog is small and static, that might be a viable choice and significantly simplify your development. For larger or fast-changing catalogs, however, that wouldn’t be a good decision, as all cached product-description pages would become obsolete as soon as a single product was changed. In these scenarios, it’s better to track the row modification date and time—the date and time when an individual product description was changed.
To track the modification date and time of each row in a database table, we need a new column (conveniently named LastModified). The SQL command in Listing 6 adds this column to our Products table.
Listing 6 LastModified column added to the Products table.
ALTER TABLE Products ADD LastModified datetime DEFAULT GETDATE()
The changes to individual rows in the Products table are detected with a new UPDATE trigger (see Listing 7). This trigger uses the inserted logical table to detect changed rows, and updates the LastModified field in each modified row.
Listing 7 Product-modification trigger.
CREATE TRIGGER ProductRowModified ON Products AFTER UPDATE AS BEGIN SET NOCOUNT ON UPDATE Products SET LastModified = GETDATE() FROM Products INNER JOIN Inserted ON (Products.ProductID = Inserted.ProductID) END
There’s no need to define additional stored procedures to retrieve the product modification date/time; when the ASP/PHP page retrieves all other product data, it can also retrieve the value of the LastModified column and adjust the value of the Last-Modified HTTP header accordingly.