创建触发器以将记录从一个表插入到另一个表.在触发器中获取插入的值 [英] Create a trigger to insert records from a table to another one. Get inserted values in a trigger

问题描述

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

I have two tables tbl_PurchaseDetails and tbl_ItemDetails. I need to insert some rows into tbl_ItemDetails from tbl_PurchaseDetails, right after it is inserted in tbl_PurchaseDetails. I know the problem but i am unable to solve it. Please help.

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

I have written the following code for the trigger:

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.它抛出以下错误:

And now when I insert into tbl_PurchaseDetails the rows are added to tbl_PurchaseDetails but not to tbl_ItemDetails. It throws the following error:

消息 515，级别 16，状态 2，过程触发器_UpdateItemDetails，第 11 行
无法将值 NULL 插入列PurchaseID"、表dbStockHandling.dbo.tbl_ItemDetails"；列不允许空值.插入失败.

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

My question is how to get the inserted values from tbl_PurchaseDetails so that the trigger can insert them into tbl_ItemDetails?

推荐答案

请尝试:

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 列中.

and make sure that you are inserting a NOT NULL value to column PurchaseID of table tbl_PurchaseDetails.

这篇关于创建触发器以将记录从一个表插入到另一个表.在触发器中获取插入的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！