CODEWITHSUNDEEP
sql-server
pinchetpooche
pinchetpooche

Reputation: 39

Update trigger from select statement loop

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

Upvotes: 2

Views: 34

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 3

Related Questions