How to tell if record in database was modified by two tasks

Question

Not sure if this belongs on SO or one of their other sites, let me know if I should move it.

Here's what I'm working with:
Its a very old eCommerce system, which has a table of products. This table of products gets updated by two files every night through Windows Task Scheduler. One file has product information (name, description, color, manufacturer, etc), the other file has information such as the pricing, available stock, warehouse location. The process of parsing these files and updating the products in the database takes multiple hours. I don't have control over these two files.

I need a way to determine if both files updated the product in the database. I can only display products to users that have information from both files. If the product was only updated by one, its considered incomplete and cannot be displayed.

As of right now, I'm thinking to add a trigger to add/update to count+1 and save to a new column each time a record/product is modified by either of the two tasks. Then make another task to check if that column is equal to 2 and do whats required to display or hide. But I hope there might be some other ideas out there?

Answer 1

In order to not have to worry about a complete regression test of the legacy process, I would opt for using a trigger to log updates on the Product table to a second table.

The trigger could determine the process being run by checking the columns updated. Then I would create a simple query to return all products that had been updated by both processes.

/* Test tables */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product')
  DROP TABLE Product

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductChangeLog')
  DROP TABLE ProductChangeLog
GO

CREATE TABLE Product (
  ProductID INT NOT NULL PRIMARY KEY, 
  ProductName VARCHAR(50) NOT NULL,
  ProductDescription VARCHAR(100) NOT NULL,
  ProductPrice MONEY NOT NULL,
  ProductAvailQty INT NOT NULL
) 

CREATE TABLE ProductChangeLog (
  ChangeLogID INT NOT NULL IDENTITY(1,1), 
  ProductID INT NOT NULL,
  ChangeTime DATETIME NOT NULL,
  ChangeType VARCHAR(50) NOT NULL,
) 
GO 

CREATE TRIGGER tr_Product ON Product 
FOR UPDATE
AS

  INSERT ProductChangeLog (ProductID, ChangeTime, ChangeType) 
  SELECT 
    ProductID,
    GETDATE(),
    -- Log the process which was run depending on columns modified
    CASE WHEN UPDATE(ProductName) OR UPDATE(ProductDescription)
         THEN 'Process1'
         WHEN UPDATE(ProductPrice) OR UPDATE(ProductAvailQty)
         THEN 'Process2'
         ELSE 'Neither'
    END 
  FROM inserted
GO 

INSERT Product 
SELECT 1, 'Blue V-Neck', 'Sweater', 25.00, 100 UNION ALL 
SELECT 2, 'Green V-Neck', 'Sweater', 30.00, 200 UNION ALL
SELECT 3, 'Black crew', 'T-Shirt', 10.00, 45

/* Change two products in first process */
UPDATE Product 
SET ProductDescription = 'Men''s Sweater' 
WHERE ProductDescription = 'Sweater'

/* Change three products in second process */
UPDATE Product
SET ProductPrice = ProductPrice + 10

/* See all changes */
SELECT * 
FROM ProductChangeLog 

DECLARE @today DATETIME
SET @today = DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) 

/* See all products changed today by both processes */
SELECT * 
FROM Product p 
WHERE EXISTS (
  SELECT * 
  FROM ProductChangeLog 
  WHERE ChangeType = 'Process1'
    AND ChangeTime > @today 
    AND ProductID = p.ProductID 
) 
AND EXISTS ( 
  SELECT * 
  FROM ProductChangeLog 
  WHERE ChangeType = 'Process2'
    AND ChangeTime > @today 
    AND ProductID = p.ProductID 
) 

*Tested in SQL Server 2005