Help with SUM in a trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with SUM in a trigger

Author

Topic

Phil H
Starting Member

3 Posts

Posted - 2008-03-05 : 05:50:51

Hi,

I have just written my first trigger which seemed to work except for one SUM statement and I have no idea why. I'd appreciate any help.

Basically we have a WasteCountTotal table which holds a total count of waste, which is calculated by summing all the waste counts in the waste count detail for that waste count header ID. This trigger enables the waste count totals to be updated whenever the details change.

In the code below I have stripped out everything except the bit I don't understand. In effect though, the waste count details are going to get updated 3 times hence the trigger will be triggered 3 times in a row.


-- PJH 29 Jan 08 Update the totals when the details file changes
ALTER TRIGGER [utr_UpdateCountTotal]
   ON  [dbo].[WasteCountDetail] 
   AFTER UPDATE, INSERT, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
        [...]
	--debug
        INSERT INTO testtemp SELECT 1, BasicUnits  
        FROM WasteCountDetail WHERE
        WasteCountTotalId IN (SELECT WasteCountTotalId FROM INSERTED) 

        INSERT INTO testtemp SELECT 2, 
        SUM(BasicUnits) 
        FROM WasteCountDetail
        WHERE WasteCountTotalId IN 
        (SELECT WasteCountTotalId FROM INSERTED) 
        GROUP BY WasteCountTotalId


END

The results I get from this are:

1 0
1 5
1 2
2 0

As you can see, there are THREE records in the query, which add up to 7. But the sum gives me 0.

Note that the record that was just changed, to which this trigger is reacting, is the first 1 (value 0).

When the trigger gets triggered again for the second update, where the second ID is updated to 5, the result are:

1 0
1 5
1 2
2 5

And finally, when the last figure 2 is updated to the database:

1 0
1 5
1 2
2 2

So it seems that the summation is only summing over the values that JUST GOT INSERTED, rather than over the whole table, which is totally not what I was expecting. Help!

Thanks,Phil

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-03-05 : 05:58:41

Just a guess, but:


WHERE WasteCountTotalId IN 
        (SELECT WasteCountTotalId FROM INSERTED)

May well be limiting your sum.

Phil H
Starting Member

3 Posts

Posted - 2008-03-05 : 06:02:35

quote:
Originally posted by RickD

Just a guess, but:

WHERE WasteCountTotalId IN
(SELECT WasteCountTotalId FROM INSERTED)

May well be limiting your sum.

Hi Rick,

Thanks for the reply. Yeah initially I thought it must be that but, both queries are virtually the same except that one is a SUM; so if the first query brings back 3 results I would have expected the second query to sum those 3 results. Which it doesn't...which is bizarre?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-03-05 : 07:44:26

Two things, when you create the temp table, what datatypes are you using?

Also, alias your sum column.

INSERT INTO testtemp SELECT 2,
SUM(BasicUnits) as BasicUnits
FROM WasteCountDetail
WHERE WasteCountTotalId IN
(SELECT WasteCountTotalId FROM INSERTED)
GROUP BY WasteCountTotalId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 08:45:03

[code]ALTER TRIGGER utr_UpdateCountTotal ON dbo.WasteCountDetail
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
SET NOCOUNT ON

INSERT TestTemp
SELECT 1,
a.BasicUnits
FROM WasteCountDetail AS a
INNER JOIN inserted AS i ON i.WasteCountTotalId = a.WasteCountTotalId

INSERT TestTemp
SELECT 2,
SUM(a.BasicUnits)
FROM WasteCountDetail AS a
INNER JOIN inserted AS i ON i.WasteCountTotalId = a.WasteCountTotalId
GROUP BY a.WasteCountTotalId
END[/code]

E 12°55'05.25"
N 56°04'39.16"

Phil H
Starting Member

3 Posts

Posted - 2008-03-05 : 13:00:15

Hi,

Thanks for your suggestions I'll try both tomorrow and see what ahppens

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources