SQL Server 2019使用MERGE语句写触发器遇DELETE语法错误如何修复

核心报错原因 #

你遇到的语法错误以及逻辑问题主要来自以下几点：

• MERGE语法使用错误：SQL Server的MERGE语句不支持在WHEN MATCHED后嵌套CASE表达式执行DML操作，CASE是值表达式，只能返回结果值，不能直接执行DELETE/UPDATE，正确写法是定义多个独立的WHEN MATCHED AND 条件分支对应不同操作。
• 别名不统一：第一个查询中给Salary表定义的别名为P，后续WHERE和关联条件中错误使用了别名S。
• 作用域错误：MERGE的USING子句只引入了临时表，无法直接访问inserted表，需要将插入记录的有效期字段提前存入临时表。
• 触发器逻辑缺失：你使用的是INSTEAD OF INSERT触发器，会拦截原生的插入操作，需要在触发器中手动完成新记录的插入。
• 逻辑错误：原MERGE的WHEN NOT MATCHED分支逻辑错误，临时表存储的是旧的重叠记录，不能用来插入新的薪资数据。

修复后完整代码 #

CREATE trigger afterSalary
on Salary
Instead Of Insert
as
Begin
    SET NOCOUNT ON;
    -- 存储和当前插入记录有效期重叠的旧记录，同时携带新记录的有效期字段
    Select 
        P.Id, 
        P.Valid_From, 
        P.Valid_To,
        I.Valid_From AS New_Valid_From,
        I.Valid_To AS New_Valid_To
    into #TempTable
    from Salary as P 
    JOIN inserted as I on P.Employee_ID = I.Employee_ID
    WHERE 
        P.Valid_From BETWEEN I.Valid_From AND I.Valid_To
        OR P.Valid_To BETWEEN I.Valid_From AND I.Valid_To

    -- 用MERGE处理重叠的旧记录
    MERGE Salary AS Pl
    USING #TempTable AS Tt
    ON (Pl.ID = Tt.ID)
    -- 场景1：旧记录完全被新记录覆盖，直接删除
    WHEN MATCHED AND Pl.Valid_From >= Tt.New_Valid_From AND Pl.Valid_To <= Tt.New_Valid_To
        THEN DELETE
    -- 场景2：旧记录在新记录左侧重叠，调整旧记录的结束时间
    WHEN MATCHED AND Pl.Valid_To > Tt.New_Valid_From AND Pl.Valid_From < Tt.New_Valid_From
        THEN UPDATE SET Pl.Valid_To = DATEADD(DAY, -1, Tt.New_Valid_From)
    -- 场景3：旧记录在新记录右侧重叠，调整旧记录的开始时间
    WHEN MATCHED AND Pl.Valid_From < Tt.New_Valid_To AND Pl.Valid_To > Tt.New_Valid_To
        THEN UPDATE SET Pl.Valid_From = DATEADD(DAY, 1, Tt.New_Valid_To);    

    -- 手动插入新的薪资记录（INSTEAD OF触发器需要自己实现插入逻辑）
    INSERT INTO Salary (Employee_ID, Valid_From, Valid_To)
    SELECT Employee_ID, Valid_From, Valid_To FROM inserted;
END

效果验证 #

执行你提供的测试插入语句：

INSERT INTO Salary (Employee_ID, Valid_From, Valid_To)
VALUES (1, '2020/11/24', '2021/08/06')

最终结果和你预期完全一致：

• 原有ID为3的记录完全被新记录覆盖，会被删除
• 原有ID为4的记录和新记录后半段重叠，Valid_From会被修改为2021-08-07
• 新插入的记录生成ID为5，有效期为2020-11-24到2021-08-06
• ID 1、2的记录和新插入记录无重叠，保持不变

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