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

Question

我有两个桌子tbl_PurchaseDetails和tbl_ItemDetails。我需要一些行插入tbl_ItemDetails从tbl_PurchaseDetails，它被插入之后tbl_PurchaseDetails。我知道这个问题，但我无法解决。请帮忙。

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

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。它引发以下错误：

_消息515，级别16，状态2，过程trigger_UpdateItemDetails，行11

无法将值NULL插入表’dbStockHandling.dbo.tbl_ItemDetails’的列’PurchaseID’中；列不允许为空。INSERT失败。_

我的问题是如何从中获取插入的值，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要PurchaseID在table的列中插入一个值tbl_PurchaseDetails。