Multiple Row Update SQL Trigger from Single Update SQL Statement

Question

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

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.

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

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

Stipple

The inserted table can contain multiple rows. I advise everyone against using triggers. They are too hard to get right, and even if you manage, the result is very hard to maintain. – Marelda

Well... I can in theory do the update with post-update SQL statements, and as long as INSERT and DELETE work ok, this would be a possible second-best solution. But I would still very much want to know how to make this trigger right... I am using only one set of triggers because in the circumstance it is exactly what I need, and offers the best solution :) Thanks for your comment. – Stipple

@Stipple Triggers are a non-preferred solution for many reasons. As Andromar says, they are hard to write and to maintain. They also adversely affect the performance of INSERT, UPDATE and DELETE, make transactions more complicated, can increase the chance of deadlocks, and are a real Pain for operational tasks. But the biggest reason is that they are hidden from normal view, making them effectively "Magic". Triggers constantly trip up DBAs and developers alike because they forget about them, resulting in many long, baffling support issues ending with "Does that table have any triggers?" – Coryphaeus

On a different issue: As a general rule in SQL, any time you find yourself using a bunch of variables to hold the column values from a row or rows, you are probably doing it wrong. At the very least, you are headed in the wrong direction. – Coryphaeus

Thank you for your comments :) The reason I am using triggers is actually to make the chance of deadlocks less likely. The table I am updating from the trigger is a read-only table that is like a summary of a much larger table that is being written to very frequently. The summary table is 100% indexed, and used for viewing huge data sets, and aside of the triggers nothing writes to them. I am 'doing' SQL for over 15 years now, and indeed have never used triggers yet, although this is mostly due to ignorance ;) In this one instance I felt they were the right choice for the job. – Stipple

Answer 1

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 inserted virtual table can contain multiple rows you need to JOIN it to correctly do your UPDATE.

Gewgaw

You da man!! This is exactly what I needed, and I berate myself for not coming up with this obvious and elegant solution!! – Stipple

@peterm,I have same issue can please check the following link #26043606 – Diaconal