Update trigger from select statement loop

Question

I have the following table:

SystemList

System  Value1  Value2  Value3  Value4  Total

System1   10               30     40
System2           20
System3   10               30
System4           30              40

I have the following in a trigger that works fine but I want to iterate through each of the Systems and update the Total of each row without having to do this in actuality 92 times. Is there a way to do a lookup of the systems and total the values in that row whenever there is an insert or update?

Use Database

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
               FROM dbo.SystemList where System = 'System1') Where System = 'System1'  

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System2') Where System = 'System2'

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System3') Where System = 'System3' 

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System4') Where System = 'System4'

My actual trigger is:

USE [Database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Total]
   ON  [dbo].[SystemList]
   AFTER  INSERT,UPDATE
AS 

Begin

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
               FROM dbo.SystemList where System = 'System1') Where System = 'System1'  

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System2') Where System = 'System2'

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System3') Where System = 'System3' 

UPDATE dbo.SystemList
SET Total = (SELECT (COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0)) AS Total
           FROM dbo.SystemList where System = 'System4') Where System = 'System4'   

End

Sean Lange · Accepted Answer · 2016-04-18 15:59:50Z

3

You don't have to specify each value in a separate query like you are doing. This would be simpler.

UPDATE dbo.SystemList SET Total = COALESCE(Value1, 0) + COALESCE(Value2, 0) + COALESCE(Value3, 0) + COALESCE(Value4, 0))

Or you can do this even easier. Use a computed column in your table instead and you don't even need a trigger.

Alter TABLE SystemList
    Add Total as isnull(Value1, 0) + isnull(Value2, 0) + isnull(Value3, 0) + isnull(Value4, 0)

https://msdn.microsoft.com/en-us/library/ms188300.aspx

answered Apr 18, 2016 at 15:59

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pinchetpooche Over a year ago

The first method worked, the second gave me very strange values. It seems to work if there is only one value in one column but not if there is a value in multiple columns.

Collectives™ on Stack Overflow

Update trigger from select statement loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related