sql - 创建一个触发器以将记录从一个表插入另一个表。获取触发器中插入的值

Question

我有两个表tbl_PurchaseDetails和tbl_ItemDetails。我需要在插入 tbl_PurchaseDetails 后立即将一些行从 tbl_PurchaseDetails 插入到 tbl_ItemDetails 中。我知道问题所在，但我无法解决它。请帮忙。

我为触发器编写了以下代码:

CREATE TRIGGER trigger_UpdateItemDetails ON tbl_PurchaseDetails
FOR INSERT AS
DECLARE @PurchaseID VARCHAR(20)
DECLARE @Quantity INT
DECLARE @WarehouseID VARCHAR(20)

SELECT @PurchaseID=(PurchaseID) FROM INSERTED
SELECT @Quantity=(ItemQuantity) FROM INSERTED
SELECT @WarehouseID=(WarehouseID) FROM INSERTED

INSERT INTO 
tbl_ItemDetails
(PurchaseID,Quantity,WarehouseID)
VALUES
(
@PurchaseID,@Quantity,@WarehouseID
)

现在，当我插入 tbl_PurchaseDetails 时，行将添加到 tbl_PurchaseDetails，但不会添加到 tbl_ItemDetails。它抛出以下错误:

Msg 515, Level 16, State 2, Procedure trigger_UpdateItemDetails, Line 11
Cannot insert the value NULL into column 'PurchaseID', table 'dbStockHandling.dbo.tbl_ItemDetails'; column does not allow nulls. INSERT fails.

我的问题是如何从 tbl_PurchaseDetails 获取插入的值，以便触发器可以将它们插入到 tbl_ItemDetails 中？

Answer 1

请尝试:

CREATE TRIGGER trigger_UpdateItemDetails ON tbl_PurchaseDetails
FOR INSERT AS
BEGIN

    INSERT INTO 
    tbl_ItemDetails
    (
        PurchaseID,
        Quantity,
        WarehouseID
    )
    SELECT 
        PurchaseID, 
        ItemQuantity, 
        WarehouseID
    FROM 
        INSERTED
END

并确保您将 NOT NULL 值插入到表 tbl_PurchaseDetails 的 PurchaseID 列中。