SQL server中如何当明细表有增改删变化时就向汇总表里不重复的汇总一次

Question

当入库记录表有增改删时如何让他向汇总表里汇总一次，并且不重复只汇总有变化的数据，或者不重复汇总

Answer 1

这种需求都是用触发器完成，这里的inserted 和 deleted 表是触发器被激活时创建的虚拟表。

CREATE TRIGGER trg_Update ON [入库记录]
AFTER INSERT, UPDATE, DELETE AS
BEGIN
    IF EXISTS(SELECT * FROM inserted)
        BEGIN
            INSERT INTO [入库汇总$]([子类别], [类别], [入库存量], [金额], [最近入库日期])
            SELECT DISTINCT [子类别], [类别], SUM([入库存量]), SUM([金额]), MAX([入库日期])
            FROM [入库记录]
            GROUP BY [子类别], [类别];
        END

    IF EXISTS(SELECT * FROM deleted)
        BEGIN
            DELETE s
            FROM [入库汇总$] s
            LEFT JOIN [入库记录] r ON s.[子类别] = r.[子类别] AND s.[类别] = r.[类别]
            WHERE r.[子类别] IS NULL;
        END
END;