来自单更新SQL语句的多行更新SQL触发器 [英] Multiple Row Update SQL Trigger from Single Update SQL Statement

问题描述

好的.我对SQL触发器很陌生，并且对它们有一些问题.插入触发器也可以，删除触发器也可以.最初，对多行进行删除只会删除一个，但是我设法自己找出一个:)

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 :)

但是，即使进行了广泛的搜索(在此处和在Google上)，我也无法找到令人满意的UPDATE触发器答案.如果我进行类似的更新

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

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.

我使用的触发器是:

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.

我在Stack Overflow上看到的示例使我有些困惑，或者似乎无效-例如，似乎大多数示例仅处理第二个/另一个表中的一个值，而不是像我正在尝试的那样处理一大堆去做.那些似乎在多个值上起作用的东西，我听不懂:(

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 :(

因此，经过大约2个小时的搜索，我放弃了，希望您能为我提供帮助:)我意识到这是一个触发器新手问题，尽管我知道我的MS-SQL，但是触发器是我从未使用过的东西， 到现在.因此，非常欢迎任何帮助:)W

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

推荐答案

似乎您需要这样的东西

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

由于 insertted 虚拟表可以包含多行，因此您需要 JOIN 才能正确执行 UPDATE .

Since inserted virtual table can contain multiple rows you need to JOIN it to correctly do your UPDATE.

这篇关于来自单更新SQL语句的多行更新SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！