如何用SQL Server触发器保留log表同ID最近3条更新记录？

要实现log表中每个ID仅保留最近3条更新记录，可通过创建AFTER INSERT, UPDATE触发器完成，每次插入或更新记录后自动清理旧数据。以下是具体实现步骤和代码：

前提假设 #

假设你的log表包含以下核心字段（可根据实际表结构调整）：

• ID：分组标识字段（如用户ID、业务ID）
• UpdateTime：记录更新/插入时间的字段（用于判断记录新旧，若表中无此字段，需先添加）

若缺少时间字段，可先执行以下语句添加：

ALTER TABLE log ADD UpdateTime DATETIME DEFAULT GETDATE();

创建触发器 #

CREATE TRIGGER trg_KeepLatest3Logs
ON log
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 用CTE给每个ID的记录按时间倒序排名
    WITH RankedLogs AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY UpdateTime DESC) AS LogRank
        FROM log
        WHERE ID IN (SELECT DISTINCT ID FROM inserted) -- 仅处理本次触发涉及的ID，提升效率
    )
    -- 删除排名超过3的旧记录
    DELETE FROM RankedLogs
    WHERE LogRank > 3;
END;

代码解释 #

• 触发时机：AFTER INSERT, UPDATE确保每次插入新记录或更新现有记录后，自动执行清理逻辑。
• SET NOCOUNT ON：避免触发器返回额外的行数统计信息，防止干扰应用程序的逻辑处理。
• CTE与窗口函数：
  • PARTITION BY ID：按ID分组，每个ID单独处理。
  • ORDER BY UpdateTime DESC：按时间倒序排列，最新的记录排名为1，次新为2，第三为3。
  • WHERE ID IN (SELECT DISTINCT ID FROM inserted)：只针对本次插入/更新涉及的ID进行处理，避免全表扫描，提升性能。
• 删除逻辑：直接删除CTE中排名大于3的记录，即每个ID保留最新的3条。

注意事项 #

• 若你的时间字段不是UpdateTime，需替换为表中实际用于判断记录先后的字段（如CreateTime、LogTime）。
• 若ID字段类型不是INT，需对应调整代码中的类型匹配。
• 测试时可插入多条同一ID的记录，验证是否仅保留最新3条；若删除失败，整个插入/更新操作会回滚，确保数据一致性。

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