来自单个更新 SQL 语句的多行更新 SQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16625014/
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
Multiple Row Update SQL Trigger from Single Update SQL Statement
提问by Irresistance
Ok. I am quite new to SQL triggers and have had some issues with them. Insert trigger works just fine, and the Delete trigger also. At first, doing a delete on multiple rows would only delete one, but I managed to figure that one out for myself :)
好的。我对 SQL 触发器很陌生，并且遇到了一些问题。插入触发器工作得很好，删除触发器也是如此。起初，对多行进行删除只会删除一个，但我设法为自己找出了一个:)
However, even after extensive searching (here and on Google) I am unable to find a satisfactory answer to the UPDATE trigger that I have. If I do an update like
但是，即使经过大量搜索（此处和 Google 上），我也无法找到对我拥有的 UPDATE 触发器的满意答案。如果我做一个更新
UPDATE Customers Set CustomerUser = 0 Where CustomerStatus = 3
Then unfortunately, only the one record would be updated, and the other would remain as they were. Obviously, this is no good.
不幸的是，只有一条记录会被更新，而另一条则保持原样。显然，这不好。
The trigger I am using is:
我使用的触发器是：
ALTER TRIGGER [dbo].[TRG_TriggerName] ON [dbo].[USER_Customers]
FOR UPDATE
AS
declare @customerid int;
declare @customervenue int;
declare @customeruser int;
declare @customerarea int;
declare @customerevent int;
declare @customerproject int;
declare @customerstatus int;
select @customerid=i.CustomerID from inserted i;
select @customervenue=i.CustomerVenue from inserted i;
select @customerarea=i.CustomerArea from inserted i;
select @customerevent=i.CustomerEvent from inserted i;
select @customerproject=i.CustomerProject from inserted i;
select @customeruser=i.CustomerUser from inserted i;
select @customerstatus=i.CustomerStatus from inserted i;
Update USER_Instances Set InstanceArea = @customerarea, InstanceVenue = @customervenue, InstanceUser = @customeruser, InstanceStatus = @customerstatus, InstanceEvent = @customerevent, InstanceLastUpdate = GetDate() Where InstanceObject = 17 AND InstanceIdentity = @customerid
GO
As you will immediately realize, this trigger is great - if you want to update just one record. Otherwise, it fails. Now - the main question here would be - How do I catch all the records that need updating, and update them all in one trigger action.
您将立即意识到，此触发器非常有用 - 如果您只想更新一条记录。否则，它失败。现在 - 这里的主要问题是 - 我如何捕获所有需要更新的记录，并在一个触发器操作中更新它们。
The examples I have seen here on Stack Overflow confuse me somewhat, or seem ineffective - for instance, it seems most of them deal with updating just ONE value in a second/other table, and not a whole bunch like I am trying to do. The ones that appear to work on multiple values, I can not understand :(
我在 Stack Overflow 上看到的示例让我有些困惑，或者似乎无效 - 例如，似乎大多数示例只更新第二个/其他表中的一个值，而不是像我试图做的那样一大堆。那些似乎适用于多个值的，我无法理解:(
So after about 2 hours of searches, I give up, and hope that you can help me :) I realize this is a trigger-newbie issue, and though I know my MS-SQL, triggers are something I have never used, until now. So any help is greatly welcome :) W
所以经过大约 2 个小时的搜索，我放弃了，希望你能帮助我 :) 我意识到这是一个触发器新手问题，虽然我知道我的 MS-SQL，但触发器是我从未使用过的，直到现在. 所以非常欢迎任何帮助:) W
回答by peterm
It seems that you need something like this
看来你需要这样的东西
ALTER TRIGGER [dbo].[TRG_TriggerName] ON [dbo].[USER_Customers]
FOR UPDATE
AS
UPDATE USER_Instances
SET InstanceArea = i.CustomerArea,
InstanceVenue = i.CustomerVenue,
InstanceUser = i.CustomerUser,
InstanceStatus = i.CustomerStatus,
InstanceEvent = i.CustomerEvent,
InstanceLastUpdate = GetDate()
FROM USER_Instances JOIN inserted i
ON InstanceIdentity = i.CustomerID AND InstanceObject = 17
Since insertedvirtual table can contain multiple rows you need to JOINit to correctly do your UPDATE.
由于inserted虚拟表可以包含多行，因此您需要使用JOIN它才能正确执行UPDATE.
