SQL Server 2017：创建Invoice表INSERT触发器，无对应子项时设总和为0

现有一对多关系的两张表：Invoice（主表）和Invoice_item（子表）。Invoice表的Number_sold_total和Item_price_total字段用于存储对应Invoice_ID下Invoice_item表中Number_sold和Item_price的总和。要求创建INSERT触发器，当插入的Invoice_ID在Invoice_item表中无匹配记录时，将这两个总和字段设为0；若有匹配记录，则自动计算对应总和。

建表语句 #

CREATE TABLE [Invoice] (
    [Invoice_ID] [int] NOT NULL,
    [Number_sold_total] [int] NOT NULL,
    [Item_price_total] [decimal] NOT NULL,
    PRIMARY KEY ([Invoice_ID])
);

CREATE TABLE [Invoice_item] (
    [Invoice_item_ID] [int] NOT NULL,
    [Invoice_ID] [int] NOT NULL,
    [Number_sold] [int] NOT NULL,
    [Item_price] [decimal] NOT NULL,
    PRIMARY KEY ([Invoice_item_ID],[Invoice_ID]),
    FOREIGN KEY ([Invoice_ID]) REFERENCES [Invoice]([Invoice_ID])
);

触发器实现代码 #

CREATE TRIGGER trg_Invoice_Insert
ON [Invoice]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    -- 更新插入的Invoice记录，根据Invoice_item数据计算或设置总和
    UPDATE i
    SET 
        Number_sold_total = ISNULL(it.Number_sold_sum, 0),
        Item_price_total = ISNULL(it.Item_price_sum, 0)
    FROM inserted i
    LEFT JOIN (
        SELECT 
            Invoice_ID,
            SUM(Number_sold) AS Number_sold_sum,
            SUM(Item_price) AS Item_price_sum
        FROM Invoice_item
        GROUP BY Invoice_ID
    ) it ON i.Invoice_ID = it.Invoice_ID;
END;

逻辑说明 #

• 使用AFTER INSERT触发时机，确保在Invoice记录插入完成后执行总和计算/赋值操作
• 通过LEFT JOIN关联插入的记录和Invoice_item的聚合结果，覆盖有子表记录和无子表记录两种场景
• 用ISNULL函数将聚合结果为空的情况转为0，满足无对应子表记录时的赋值要求
• SET NOCOUNT ON用于屏蔽触发器执行过程中的计数返回信息，避免干扰客户端逻辑

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