Help with Trigger to SUM a child column

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 Trigger to SUM a child column

Author

Topic

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 05:05:35

I have been trying for days to work out the syntax on setting a trigger to update a parent total column with the sum of it's child records every time the child record is either updated or inserted with a new record.

The parent table is dbo.Recipes, the child tables are dbo.RecipeIngredientds and dbo.Ingredients.
The field I want to update in the parent table is Calories which should be the sum from the child table column called Quantity multiply
the child table column called CaloriesPerUnit where the RecipeID equals each other in both tables.

I wrote the trigger below but,When I execute it,I get Invalid column name error.I guess I should join RecipeIngredients and Ingredients Table,butI couldn't make it.Can someone help me write this trigger?

UPDATE p
SET p.Calories1=pc.Calories1
FROM Recipes p
INNER JOIN (SELECT RecipeID,SUM(Quantity1*CaloriesPerUnit) AS Calories1
FROM RecipeIngredients pc
WHERE EXISTS (SELECT 1 FROM INSERTED WHERE RecipeID =pc.RecipeID)
GROUP BY RecipeID) pc
ON pc.RecipeID=p.RecipeID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-10 : 05:23:55

i am guessing that the Quantity is from the RecipeIngredients and CaloriesPerUnit is from Ingredients


UPDATE p
SET    p.Calories = pc.Calories1
FROM   Recipes p
       INNER JOIN 
       (
            SELECT ri.RecipeID, SUM(ri.Quantity1 * i.CaloriesPerUnit) AS Calories1
            FROM   RecipeIngredients ri
                   INNER JOIN Ingredients i ON ri.RecipeID = i.RecipeID
            GROUP BY ri,RecipeID
       ) pc ON pc.RecipeID = p.RecipeID
WHERE  EXISTS (SELECT 1 FROM INSERTED i WHERE i.RecipeID = pc.RecipeID)

KH
[spoiler]Time is always against us[/spoiler]

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 05:32:42

quote:

Originally posted by khtan


UPDATE p
SET    p.Calories = pc.Calories1
FROM   Recipes p
       INNER JOIN 
       (
            SELECT RecipeID,SUM(Quantity1*CaloriesPerUnit) AS Calories1
            FROM   RecipeIngredients pc
            GROUP BY RecipeID
       ) pc ON pc.RecipeID = p.RecipeID
WHERE  EXISTS (SELECT 1 FROM INSERTED i WHERE i.RecipeID = pc.RecipeID)

KH
[spoiler]Time is always against us[/spoiler]

khtan I get still same error,you are right guessing that the Quantity is from the RecipeIngredients and CaloriesPerUnit is from Ingredients.I am guessing that the query needs one more Inner Join which is between RecipeIngredients and Ingredients

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-10 : 05:35:26

Please refer to my post. I have accidentally submitted it before i finished editing it. Please refer to the updated query again

KH
[spoiler]Time is always against us[/spoiler]

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 05:42:30

quote:
Originally posted by khtan

Please refer to my post. I have accidentally submitted it before i finished editing it. Please refer to the updated query again

KH
[spoiler]Time is always against us[/spoiler]

I executed your updated query,but I get Invalid column name 'ri' error.

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 06:04:40

this is work:))

quote:

UPDATE p
SET p.Calories1 = pc.Calories1
FROM Recipes p
INNER JOIN
(
SELECT ri.RecipeID, SUM(ri.Quantity1 * i.CaloriesPerUnit) AS Calories1
FROM RecipeIngredients ri
INNER JOIN Ingredients i ON ri.IngredientID = i.IngredientID
GROUP BY RecipeID
) pc ON pc.RecipeID = p.RecipeID
WHERE EXISTS (SELECT 1 FROM INSERTED i WHERE i.RecipeID = pc.RecipeID)

khtan,Thank you for your reply.I edited your trigger and works fine now.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-10 : 06:12:05

welcome

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources