SQL Server 2019：如何用触发器从双表自动插入更新EmployeeSales数据？

根据你的表结构需求，我们需要创建插入触发器和更新触发器，分别处理OrderInfo和SalesInfo变动时同步数据到EmployeeSales的逻辑。以下是具体实现代码和说明：

1. 插入同步触发器 #

需要同时处理OrderInfo和SalesInfo的插入操作，确保无论先插入哪张表的数据，EmployeeSales都能同步生成记录。

当OrderInfo插入新记录时同步 #

CREATE TRIGGER trg_OrderInfo_Insert_SyncEmployeeSales
ON OrderInfo
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeSales (OdrId, EmpId, ProductId, ProductName, ProductQuan, ProductPrice)
    SELECT 
        i.OrderId AS OdrId,
        i.EmployeeId AS EmpId,
        s.ProdId AS ProductId,
        s.ProdName AS ProductName,
        s.ProdQuantity AS ProductQuan,
        s.ProdPrice AS ProductPrice
    FROM INSERTED i
    JOIN SalesInfo s 
        ON i.OrderId = s.OdrId 
        AND i.ProductId = s.ProdId
    WHERE NOT EXISTS (
        SELECT 1 FROM EmployeeSales es 
        WHERE es.OdrId = i.OrderId AND es.ProductId = s.ProdId
    );
END;
GO

当SalesInfo插入新记录时同步 #

CREATE TRIGGER trg_SalesInfo_Insert_SyncEmployeeSales
ON SalesInfo
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EmployeeSales (OdrId, EmpId, ProductId, ProductName, ProductQuan, ProductPrice)
    SELECT 
        i.OdrId,
        o.EmployeeId AS EmpId,
        i.ProdId AS ProductId,
        i.ProdName AS ProductName,
        i.ProdQuantity AS ProductQuan,
        i.ProdPrice AS ProductPrice
    FROM INSERTED i
    JOIN OrderInfo o 
        ON i.OdrId = o.OrderId 
        AND i.ProdId = o.ProductId
    WHERE NOT EXISTS (
        SELECT 1 FROM EmployeeSales es 
        WHERE es.OdrId = i.OdrId AND es.ProductId = i.ProdId
    );
END;
GO

2. 更新同步触发器 #

针对OrderInfo和SalesInfo的字段更新，同步修改EmployeeSales的对应内容。

当OrderInfo的EmployeeId更新时同步 #

CREATE TRIGGER trg_OrderInfo_Update_SyncEmployeeSales
ON OrderInfo
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅当EmployeeId字段发生变化时执行更新
    IF UPDATE(EmployeeId)
    BEGIN
        UPDATE es
        SET es.EmpId = i.EmployeeId
        FROM EmployeeSales es
        JOIN INSERTED i 
            ON es.OdrId = i.OrderId 
            AND es.ProductId = i.ProductId
        WHERE es.EmpId <> i.EmployeeId;
    END;
END;
GO

当SalesInfo的产品字段更新时同步 #

CREATE TRIGGER trg_SalesInfo_Update_SyncEmployeeSales
ON SalesInfo
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅当产品相关字段发生变化时执行更新
    IF UPDATE(ProdName) OR UPDATE(ProdQuantity) OR UPDATE(ProdPrice)
    BEGIN
        UPDATE es
        SET 
            es.ProductName = i.ProdName,
            es.ProductQuan = i.ProdQuantity,
            es.ProductPrice = i.ProdPrice
        FROM EmployeeSales es
        JOIN INSERTED i 
            ON es.OdrId = i.OdrId 
            AND es.ProductId = i.ProdId
        WHERE 
            es.ProductName <> i.ProdName 
            OR es.ProductQuan <> i.ProdQuantity 
            OR es.ProductPrice <> i.ProdPrice;
    END;
END;
GO

注意事项 #

• 确保OrderInfo.OrderId与SalesInfo.OdrId、OrderInfo.ProductId与SalesInfo.ProdId之间的关联关系正确，这是触发器同步的核心依据
• 给关联字段创建索引，提升触发器执行效率，避免批量操作时性能低下
• 测试时覆盖批量插入/更新、字段无变化的更新等场景，验证触发器逻辑的正确性
• SET NOCOUNT ON用于禁止返回触发器执行时的影响行数，避免干扰应用程序的业务逻辑

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