问题描述
我想在一个表中插入值后更新多个表和值,所以我创建了一个触发器。它适合插入一行,但是一旦我插入更多行,SQL Server就会给我以下错误:
I want to update multiple tables and values after inserting values in one table so I created a trigger. It works fine for inserts of one row, but as soon I insert more rows, SQL Server gives me following error:
这是我的触发器:
CREATE TRIGGER [dbo].[tbl_Sales_ForInsert]
ON [dbo].[SALES]
FOR INSERT
AS
BEGIN
DECLARE @ITEMMODEL varchar(100)
SELECT @ITEMMODEL = ITEM_MODEL FROM inserted
UPDATE SALES
SET PROFIT = TOTAL_PRICE - (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL = @ITEMMODEL) * (SELECT RATE FROM ITEM_DETAILS WHERE ITEM_MODEL = @ITEMMODEL)
WHERE ITEM_MODEL = @ITEMMODEL
UPDATE ITEM_DETAILS
SET QUANTITY = QUANTITY - (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL = @ITEMMODEL)
WHERE ITEM_MODEL = @ITEMMODEL
--UPDATE ITEM_DETAILS SET AMOUNT = AMOUNT - (SELECT RATE FROM ITEM_DETAILS WHERE ITEM_MODEL=@ITEMMODEL) * (SELECT QUANTITY FROM SALES WHERE ITEM_MODEL=@ITEMMODEL) where ITEM_MODEL=@ITEMMODEL
END
当我第一次在 SALES
表中插入数据时,更新成功,但是第二次它给了我以上错误,请记住 ITEM_MODEL
是SALES表中的外键约束。
As I insert data in SALES
table for 1st time the update got successful but for 2nd time it gives me above error remember ITEM_MODEL
is foreign key constraint in SALES table.
我一直在遭受这个错误的困扰,有人可以帮我吗?
I have been suffering with this error can anyone help me please?
推荐答案
您的基本缺陷是,您似乎希望触发器每行一次被触发-这是否在SQL Server中是这种情况。取而代之的是,触发器将每条语句触发一次,并且伪表已插入
可能包含多行。
Your fundamental flaw is that you seem to expect the trigger to be fired once per row - this is NOT the case in SQL Server. Instead, the trigger fires once per statement, and the pseudo table Inserted
might contain multiple rows.
鉴于该表可能包含多行-您希望在此处选择哪一行?
Given that that table might contain multiple rows - which one do you expect will be selected here??
SELECT @ITEMMODEL = ITEM_MODEL FROM inserted
它是未定义的-您可能会从中获取值已插入
中的任意行。
It's undefined - you might get the values from arbitrary rows in Inserted
.
您需要使用知识重写整个触发器插入
将包含多行!您需要使用基于集合的操作-不要期望在插入
中只有一行!
You need to rewrite your entire trigger with the knowledge the Inserted
WILL contain multiple rows! You need to work with set-based operations - don't expect just a single row in Inserted
!
所以在您的情况下,您的触发代码应如下所示:
So in your case, your trigger code should look something like this:
CREATE TRIGGER [dbo].[tbl_Sales_ForInsert]
ON [dbo].[SALES]
FOR INSERT
AS
BEGIN
-- update the dbo.Sales table, set "PROFIT" to the difference of
-- TOTAL_PRICE and (QUANTITY * RATE) from the "Inserted" pseudo table
UPDATE s
SET s.PROFIT = i.TOTAL_PRICE - (i.QUANTITY * i.RATE)
FROM dbo.Sales s
INNER JOIN Inserted i ON i.ITEM_MODEL = s.ITEM_MODEL
-- update the dbo.ITEM_DETAILS table
UPDATE id
SET id.QUANTITY = id.QUANTITY - i.Quantity
FROM dbo.ITEM_DETAILS id
INNER JOIN Inserted i ON id.ITEM_MODEL = i.ITEM_MODEL
END
这篇关于子查询返回的值超过1。当子查询后跟=或将子查询用作表达式时,这是不允许的?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!