创建触发器以将记录从一个表插入到另一个表.在触发器中获取插入的值 [英] 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屋!