SQL 创建触发器以将记录从一个表插入到另一个表中。在触发器中获取插入的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15239800/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a trigger to insert records from a table to another one. Get inserted values in a trigger
提问by Abhishek Singh
I have two tables tbl_PurchaseDetailsand tbl_ItemDetails. I need to insert some rows into tbl_ItemDetailsfrom tbl_PurchaseDetails, right after it is inserted in tbl_PurchaseDetails. I know the problem but i am unable to solve it. Please help.
我有两张桌子tbl_PurchaseDetails和tbl_ItemDetails. 我需要在tbl_ItemDetailsfrom 中插入一些行tbl_PurchaseDetails，在它插入tbl_PurchaseDetails. 我知道问题所在，但我无法解决。请帮忙。
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
)
And now when I insert into tbl_PurchaseDetailsthe rows are added to tbl_PurchaseDetailsbut not to tbl_ItemDetails. It throws the following error:
现在，当我插入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.
消息 515，级别 16，状态 2，过程 trigger_UpdateItemDetails，第 11 行
无法将值 NULL 插入列“PurchaseID”、表“dbStockHandling.dbo.tbl_ItemDetails”；列不允许空值。插入失败。
My question is how to get the inserted values from tbl_PurchaseDetailsso that the trigger can insert them into tbl_ItemDetails?
我的问题是如何从中获取插入的值，tbl_PurchaseDetails以便触发器可以将它们插入到tbl_ItemDetails?
回答by TechDo
Please try:
请尝试：
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
and make sure that you are inserting a NOT NULLvalue to column PurchaseIDof table tbl_PurchaseDetails.
并确保您在table 的NOT NULL列中插入一个值。PurchaseIDtbl_PurchaseDetails
