创建INSERT触发器：按VALID_FROM顺序更新同ITEM_ID记录的VALID_UNTIL

表结构 #

ITEM_ID VARCHAR(255) NOT NULL PRIMARY KEY,
COST1 FLOAT NOT NULL,
COST2 FLOAT NOT NULL,
PRICE1 FLOAT NOT NULL,
PRICE2 FLOAT NOT NULL,
VALID_FROM DATE NOT NULL PRIMARY KEY,
VALID_UNTIL DATE NULL,

VALID_UNTIL字段默认值为'2099-12-31'。

需求 #

需要创建触发器实现以下逻辑：

• 插入新记录时，若表中存在相同ITEM_ID的记录，将该ITEM_ID下最新的记录（即VALID_UNTIL为默认值的记录）的VALID_UNTIL更新为当前插入记录的VALID_FROM
• 若不存在匹配ITEM_ID的记录，直接插入新记录
• 触发器需对每条插入记录按VALID_FROM从旧到新的顺序单独生效

原有尝试的触发器（存在问题） #

CREATE TRIGGER trg_update_valid_until
ON cal.MA_COSTS
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
    SET t.VALID_UNTIL = i.VALID_FROM
    FROM cal.MA_COSTS t
    INNER JOIN inserted i
        ON t.ITEM_ID= i.ITEM_ID
        AND t.VALID_UNTIL = '2099-12-31'
        AND t.VALID_FROM < i.VALID_FROM;
END;

问题：批量插入时，所有记录的VALID_UNTIL仍为'2099-12-31'。原因是原触发器一次性处理所有插入记录，未按VALID_FROM顺序逐条生效，导致多条插入记录同时关联旧记录，最终更新逻辑混乱。

示例测试数据 #

INSERT INTO cal.MA_COSTS (ITEM_ID, COST1, COST2, PRICE1, PRICE2, VALID_FROM)
VALUES
(2008209, 4062.7, 4062.7, 5803.85, 6732.47, '2024-12-01'),
(2008209, 4119.57, 4119.57, 5885.09, 6826.7, '2024-12-02'),
(2008209, 4150.85, 4150.85, 5929.78, 6878.54, '2024-12-06');

解决方案：修改后的触发器 #

CREATE TRIGGER trg_update_valid_until
ON cal.MA_COSTS
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 对插入的记录按ITEM_ID分组、VALID_FROM升序排序，获取每条记录的前序生效日期
    WITH InsertedOrdered AS (
        SELECT 
            ITEM_ID,
            VALID_FROM,
            LAG(VALID_FROM) OVER(PARTITION BY ITEM_ID ORDER BY VALID_FROM) AS PrevValidFrom
        FROM inserted
    ),
    -- 定位需要更新的目标记录
    TargetRecords AS (
        SELECT 
            t.*,
            io.VALID_FROM AS NewValidFrom
        FROM cal.MA_COSTS t
        INNER JOIN InsertedOrdered io
            ON t.ITEM_ID = io.ITEM_ID
            AND t.VALID_UNTIL = '2099-12-31'
            -- 处理两种情况：插入的第一条记录更新原有旧记录；后续记录更新上一条插入的记录
            AND (io.PrevValidFrom IS NULL OR t.VALID_FROM = io.PrevValidFrom)
    )
    UPDATE TargetRecords
    SET VALID_UNTIL = NewValidFrom;
END;

逻辑说明 #

1. InsertedOrdered CTE：对插入的记录按ITEM_ID分组，按VALID_FROM升序排序，用LAG函数获取每条记录的前一条记录的生效日期，确保处理顺序正确。
2. TargetRecords CTE：关联原表，定位需要更新的记录：
   • 若为该ITEM_ID的第一条插入记录，更新原表中该ITEM_ID下VALID_UNTIL为默认值的旧记录（插入前的最新记录）
   • 若为后续插入的记录，更新上一条插入的记录（因为上一条插入后VALID_UNTIL仍为默认值，需将其结束日期设为当前记录的生效日期）
3. 最终执行更新，确保每条插入记录按顺序单独生效。

测试结果 #

插入示例数据后，各记录的VALID_UNTIL值如下：

• 2024-12-01的记录：VALID_UNTIL = '2024-12-02'
• 2024-12-02的记录：VALID_UNTIL = '2024-12-06'
• 2024-12-06的记录：VALID_UNTIL = '2099-12-31'

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