Help with my first 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 my first Trigger to SUM a child column

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-07-04 : 11:47:14

I hate asking basic questions but I am new to SQL and 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. Seems so basic to me but I can't find a clear example of how to do this. I am using SQL 2008 server and Server Management Studio.

The parent table is dbo.insurance_billing, the child table is dbo.pro_collected.
The field I want to update in the parent table is sum_pro_collected which should be the sum from the child table column called pro_payment_collected where the patient_ID equals each other in both tables.

The child table will never be more than 5 or 6 rows per parent so I am not too worried about performance issues using a trigger, just need to always have the total displayed.

Can someone help me write this trigger? I truly appreciate any guidance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-04 : 11:56:30

Is it possible to avoid that kind of trigger?
I would prefer to have a computed column in the parent table.
If it is not possible to have a formular for that computed column with access to another table so it is possible to use an UDF to get the SUM from child table.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-07-04 : 12:26:46

Fred,

I am not sure what the best way is to deal with this using SQL as this is my first attempt at SQL.
My situation is that for the past 3 years I have been using Alpha Five as my web interface to their internal dbf database, which I know very well how to deal with it there. But in order to maximize the performance of my online medical data portal I want to use SQL as my back end so the question is, how best to make available to Alpha Five that sum total.
It would seem to me a calculated field would be great but I thought I read somewhere that you can't use a calculate field outside of the table itself.
Anyway, that is why I am hoping for some direction from this forum.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 03:13:22

you can do it via trigger or via UDF that webfred suggesteed

method 1: Trigger


CREATE TRIGGER pro_collected_iu
ON pro_collected
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE p
SET p.sum_pro_collected=pc.sum_pro_collected
FROM dbo.insurance_billing p
INNER JOIN (SELECT patient_ID,SUM(pro_payment_collected) AS sum_pro_collected
FROM dbo.pro_collected pc
WHERE EXISTS (SELECT 1 FROM INSERTED WHERE patient_id =pc.patient_id)
GROUP BY patient_id) pc
ON pc.patient_id=p.patient_id
END
[code]

method 2: UDF

make the column sum_pro_collected as calculated in parent table like
[code]
ALTER TABLE dbo.insurance_billing ADD sum_pro_collected AS dbo.AggregateProCollection(patient_id)


 and create this function before


CREATE FUNCTION dbo.AggregateProCollection
(@Patient_id int)
RETURNS numeric(10,2)
AS
BEGIN
DECLARE @RetVal numeric(10,2)
SELECT @RetVal=SUM(ro_payment_collected)
FROM dbo.pro_collected
WHERE patient_id=@Patient_id
RETURN @RetVal
END

scholin
Yak Posting Veteran

56 Posts

Posted - 2009-07-05 : 09:37:34

Visakh,

Thank you for taking the time to provide not only one but two complete solutions. I went with the UDF solution as you and Fred suggested - works perfectly.

Being new at SQL I would not have come up with this solution (as I have been trying to research this for over a week) and you make it look so easy!

Thank you again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-05 : 12:51:33

welcome

costelloo
Starting Member

7 Posts

Posted - 2011-12-09 : 11:30:09

If I wanna join one more table how can I modify the query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 11:52:47

quote:
Originally posted by costelloo

If I wanna join one more table how can I modify the query?

add the join inside udf code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 03:40:49

quote:
Originally posted by visakh16

quote:
Originally posted by costelloo

If I wanna join one more table how can I modify the query?

add the join inside udf code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I wanna use trigger not UDF.Where should I add JOIN keyword in the trigger above?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-10 : 03:45:41

costelloo,

Please post your question as a new thread. You will get more attention

Also refer to http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

costelloo
Starting Member

7 Posts

Posted - 2011-12-10 : 04:52:00

quote:
Originally posted by khtan

costelloo,

Please post your question as a new thread. You will get more attention

Also refer to http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

oke khtan,
Thanks for your reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:40:24

quote:
Originally posted by costelloo

quote:
Originally posted by visakh16

quote:
Originally posted by costelloo

If I wanna join one more table how can I modify the query?

add the join inside udf code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I wanna use trigger not UDF.Where should I add JOIN keyword in the trigger above?

something like


CREATE TRIGGER pro_collected_iu
ON pro_collected
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE p
SET p.sum_pro_collected=pc.sum_pro_collected
FROM dbo.insurance_billing p
INNER JOIN (SELECT patient_ID,SUM(pro_payment_collected) AS sum_pro_collected
FROM dbo.pro_collected pc
WHERE EXISTS (SELECT 1 FROM INSERTED WHERE patient_id =pc.patient_id)
GROUP BY patient_id) pc
ON pc.patient_id=p.patient_id
INNER JOIN othertable
ON ....

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources