SQL Server：菜谱数据库变更追踪及生产日志记录技术问询

Great question—logging recipe changes only when attributes differ is a common need for manufacturing or food production systems, and SQL Server has some clean ways to handle this, especially with 30 attributes to compare. Let's break down a robust implementation based on your example:

Core Logic Recap #

Your goal is to avoid redundant logs: only save a new recipe version if the current attribute set doesn't match the most recently logged version for that catalog number. Instead of writing 30 individual AND conditions (which is error-prone), we can use SQL Server's EXCEPT operator to compare the full attribute set in one go.

Full Implementation Code #

First, let's assume your tbl_recipehistory table has columns: CatalogNo (unique recipe identifier), dts (timestamp of the log), and Value1 through Value30 (your recipe attributes). Here's a stored procedure that handles the check and log insertion:

CREATE PROCEDURE LogRecipeIfChanged
    @CatalogNo VARCHAR(50),
    @Value1 INT,
    @Value2 INT,
    @Value3 INT,
    -- ... Add parameters for Value4 through Value30 here
    @Value30 INT
AS
BEGIN
    SET NOCOUNT ON;

    -- Check if current attributes differ from the most recent logged version
    IF EXISTS (
        -- Current recipe attributes as a single-row result set
        SELECT 
            @Value1 AS Value1, 
            @Value2 AS Value2, 
            @Value3 AS Value3,
            -- ... Include all Value4 to Value30 here
            @Value30 AS Value30
        EXCEPT
        -- Most recent logged recipe attributes
        SELECT TOP 1 
            Value1, 
            Value2, 
            Value3,
            -- ... Match all Value columns from the history table
            Value30
        FROM tbl_recipehistory
        WHERE CatalogNo = @CatalogNo
        ORDER BY dts DESC
    )
    BEGIN
        -- Insert the new recipe version into history
        INSERT INTO tbl_recipehistory (
            CatalogNo, 
            dts, 
            Value1, 
            Value2, 
            Value3,
            -- ... List all Value columns here
            Value30
        )
        VALUES (
            @CatalogNo,
            GETDATE(),
            @Value1,
            @Value2,
            @Value3,
            -- ... Map all parameters to their respective columns
            @Value30
        );

        -- Optional: Add additional logging to an audit table if needed
        -- INSERT INTO tbl_auditlog (CatalogNo, Action, LogTime)
        -- VALUES (@CatalogNo, 'Recipe Attribute Changed', GETDATE());
    END
END

Key Notes & Optimizations #

• Why EXCEPT? This operator compares two result sets and returns rows from the first set that don't exist in the second. It's far cleaner than writing 30 AND r.ValueX = @ValueX conditions, and less likely to miss an attribute if you add/remove properties later.
• Performance Boost: Add a composite index on tbl_recipehistory to speed up the "get most recent version" query:

  CREATE NONCLUSTERED INDEX IX_tbl_recipehistory_CatalogNo_Dts 
  ON tbl_recipehistory (CatalogNo, dts DESC);
  

  This index lets SQL Server quickly locate the latest log entry for any catalog number without scanning the entire table.
• Alternative for Table-Source Recipes: If your current recipe is stored in a table (e.g., tbl_currentrecipes) instead of passed via parameters, adjust the check like this:

  IF EXISTS (
      SELECT Value1, Value2, ..., Value30
      FROM tbl_currentrecipes
      WHERE CatalogNo = @CatalogNo
      EXCEPT
      SELECT TOP 1 Value1, Value2, ..., Value30
      FROM tbl_recipehistory
      WHERE CatalogNo = @CatalogNo
      ORDER BY dts DESC
  )
  BEGIN
      INSERT INTO tbl_recipehistory (CatalogNo, dts, Value1, ..., Value30)
      SELECT CatalogNo, GETDATE(), Value1, ..., Value30
      FROM tbl_currentrecipes
      WHERE CatalogNo = @CatalogNo;
  END
  

Edge Case Handling #

• First-Time Logging: If a recipe has no existing history, the EXCEPT check will return a row (since the right-hand result set is empty), so the procedure will insert the first log entry automatically.
• Exact Matches: If all 30 attributes match the latest log, EXCEPT returns nothing, so no redundant entry is created.

内容的提问来源于stack exchange，提问作者JPoole