创建SQL触发器：让ACTUAL行值等于REPORTED行减UNFOUNDED行

针对你的需求，我们可以创建一个AFTER INSERT, UPDATE触发器，当REPORTED或UNFOUNDED行的数据被插入或更新时，自动计算并更新同年月下ACTUAL行的对应字段值。

触发器完整代码 #

CREATE TRIGGER trg_UpdateActualValues
ON PARTONETBL
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅处理REPORTED或UNFOUNDED类型的行变更
    IF EXISTS (SELECT 1 FROM inserted WHERE REPORT_TYPE IN ('REPORTED', 'UNFOUNDED'))
    BEGIN
        -- 遍历所有受影响的年月组合
        DECLARE @MM FLOAT(8), @YYYY FLOAT(8);
        DECLARE cur CURSOR FOR
            SELECT DISTINCT MM, YYYY FROM inserted WHERE REPORT_TYPE IN ('REPORTED', 'UNFOUNDED');
        
        OPEN cur;
        FETCH NEXT FROM cur INTO @MM, @YYYY;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 获取当前年月下的REPORTED和UNFOUNDED数据
            DECLARE 
                @ReportedHomicide FLOAT(8), @UnfoundedHomicide FLOAT(8),
                @ReportedManslaughter FLOAT(8), @UnfoundedManslaughter FLOAT(8),
                @ReportedRobberyTotal FLOAT(8), @UnfoundedRobberyTotal FLOAT(8),
                @ReportedRobberyFirearm FLOAT(8), @UnfoundedRobberyFirearm FLOAT(8),
                @ReportedRobberyKnife FLOAT(8), @UnfoundedRobberyKnife FLOAT(8),
                @ReportedAssaultTotal FLOAT(8), @UnfoundedAssaultTotal FLOAT(8),
                @ReportedAssaultFirearm FLOAT(8), @UnfoundedAssaultFirearm FLOAT(8),
                @ReportedAssaultKnife FLOAT(8), @UnfoundedAssaultKnife FLOAT(8),
                @ReportedTotal FLOAT(8), @UnfoundedTotal FLOAT(8);

            -- 提取REPORTED行数据
            SELECT
                @ReportedHomicide = HOMICIDE,
                @ReportedManslaughter = MANSLAUGHTER,
                @ReportedRobberyTotal = ROBBERYTOTAL,
                @ReportedRobberyFirearm = ROBBERFIREARM,
                @ReportedRobberyKnife = ROBBERYKNIFE,
                @ReportedAssaultTotal = ASSAULTOTAL,
                @ReportedAssaultFirearm = ASSAULTFIREARM,
                @ReportedAssaultKnife = ASSAULTKNIFE,
                @ReportedTotal = TOTAL
            FROM PARTONETBL
            WHERE MM = @MM AND YYYY = @YYYY AND REPORT_TYPE = 'REPORTED';

            -- 提取UNFOUNDED行数据
            SELECT
                @UnfoundedHomicide = HOMICIDE,
                @UnfoundedManslaughter = MANSLAUGHTER,
                @UnfoundedRobberyTotal = ROBBERYTOTAL,
                @UnfoundedRobberyFirearm = ROBBERFIREARM,
                @UnfoundedRobberyKnife = ROBBERYKNIFE,
                @UnfoundedAssaultTotal = ASSAULTOTAL,
                @UnfoundedAssaultFirearm = ASSAULTFIREARM,
                @UnfoundedAssaultKnife = ASSAULTKNIFE,
                @UnfoundedTotal = TOTAL
            FROM PARTONETBL
            WHERE MM = @MM AND YYYY = @YYYY AND REPORT_TYPE = 'UNFOUNDED';

            -- 更新ACTUAL行数据
            UPDATE PARTONETBL
            SET
                HOMICIDE = ISNULL(@ReportedHomicide, 0) - ISNULL(@UnfoundedHomicide, 0),
                MANSLAUGHTER = ISNULL(@ReportedManslaughter, 0) - ISNULL(@UnfoundedManslaughter, 0),
                ROBBERYTOTAL = ISNULL(@ReportedRobberyTotal, 0) - ISNULL(@UnfoundedRobberyTotal, 0),
                ROBBERFIREARM = ISNULL(@ReportedRobberyFirearm, 0) - ISNULL(@UnfoundedRobberyFirearm, 0),
                ROBBERYKNIFE = ISNULL(@ReportedRobberyKnife, 0) - ISNULL(@UnfoundedRobberyKnife, 0),
                ASSAULTOTAL = ISNULL(@ReportedAssaultTotal, 0) - ISNULL(@UnfoundedAssaultTotal, 0),
                ASSAULTFIREARM = ISNULL(@ReportedAssaultFirearm, 0) - ISNULL(@UnfoundedAssaultFirearm, 0),
                ASSAULTKNIFE = ISNULL(@ReportedAssaultKnife, 0) - ISNULL(@UnfoundedAssaultKnife, 0),
                TOTAL = ISNULL(@ReportedTotal, 0) - ISNULL(@UnfoundedTotal, 0)
            WHERE MM = @MM AND YYYY = @YYYY AND REPORT_TYPE = 'ACTUAL';

            FETCH NEXT FROM cur INTO @MM, @YYYY;
        END

        CLOSE cur;
        DEALLOCATE cur;
    END
END

关键说明 #

1. 触发时机：使用AFTER INSERT, UPDATE，确保只有当REPORTED或UNFOUNDED行的数据被插入或修改时，才触发计算逻辑，避免不必要的性能消耗。
2. 数据安全：使用ISNULL函数处理可能的异常值，确保计算不会因数据缺失报错。
3. 批量处理：通过游标遍历所有受影响的年月组合，支持一次性插入/更新多条不同年月的数据。

额外建议 #

为了避免同一年月下出现重复的REPORT_TYPE行（比如多个REPORTED行）导致计算错误，建议添加唯一约束：

ALTER TABLE PARTONETBL
ADD CONSTRAINT UQ_PARTONETBL_MM_YYYY_REPORTTYPE
UNIQUE (MM, YYYY, REPORT_TYPE);

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