December 8, 2009 at 3:13 am
I have a table with a one-to-many relationship to a child table.
When a row is inserted into the child table, I want a column in the parent table to automatically update with the total number of rows with the parents foreign key.
How would go about doing this? Via a trigger or stored procedure?
I hope that makes sense and thanks for your help
ParentTable
=========
Id PK Identity 1,1
Childrows Int
ChildTable
=======
Id PK Identity 1,1
ParentId FK
December 8, 2009 at 8:42 am
Yes, I think a an insert trigger would be the simplest solution. I made an example for you, using the Northwind Orders and Order Details tables. 'TriggeredDetails' is the column in the parent that contains the count of child records for that OrderID:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tg_OrderDetailsInsert]
ON [dbo].[Order Details]
FOR INSERT
AS
BEGIN
update Orders set TriggeredDetails =
(select count(orderid) from [Order Details]
where orderid = i.orderid)
from Orders o
join inserted i
on o.orderid = i.orderid
END
_________________________________
seth delconte
http://sqlkeys.com
December 8, 2009 at 8:57 am
Hunnie (12/8/2009)
I have a table with a one-to-many relationship to a child table.When a row is inserted into the child table, I want a column in the parent table to automatically update with the total number of rows with the parents foreign key.
How would go about doing this? Via a trigger or stored procedure?
The more important question is "Why do you think that you need to do this?" Generally the preferred approach to provide this information is to just calculate it on request:
SELECT COUNT(*) AS ChildCount
FROM ChildTable
WHERE ParentID = @RequestedID
This approach is preferred to your approach (which is called pre-aggregation) because it *usually* has lower overhead and almost always avoids the many potential locking and simultaneity problems of pre-aggregation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 8, 2009 at 9:08 am
The more important question is "Why do you think that you need to do this?" Generally the preferred approach to provide this information is to just calculate it on request:
SELECT COUNT(*) AS ChildCount
FROM ChildTable
WHERE ParentID = @RequestedID
This approach is preferred to your approach (which is called pre-aggregation) because it *usually* has lower overhead and almost always avoids the many potential locking and simultaneity problems of pre-aggregation.
Excellent.
_________________________________
seth delconte
http://sqlkeys.com
December 8, 2009 at 9:12 am
Glad I could help 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 9, 2009 at 2:41 am
Seth - thanks for the code example.
RBarryYoung (12/8/2009)
The more important question is "Why do you think that you need to do this?" Generally the preferred approach to provide this information is to just calculate it on request:
SELECT COUNT(*) AS ChildCount
FROM ChildTable
WHERE ParentID = @RequestedID
This approach is preferred to your approach (which is called pre-aggregation) because it *usually* has lower overhead and almost always avoids the many potential locking and simultaneity problems of pre-aggregation.
I have inherited an application and have been tasked with looking for efficiencies etc. during the upgrade to sql2008. I can't really change the underlying data structure and this pre-aggregation was already in place.
Basically I am trying to reduce the no. of server/db requests that are sent by using stored procs/triggers wherever possible - that's about as far as my remit goes on this one.
Added to that, I am coming back to sql/relational databases after about 10 years away- so I'm on a sharp relearning curve too.
Thanks for your input and hopefully when we come to the next round of revisions, I can implement some better practices in this application.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply