| Author |
 Topic  |
|
|
MarionT
Starting Member
USA
2 Posts |
 Posted - 01/20/2013 : 16:58:01
|
Hello -
I think there are several ways to accomplish this, but I'm trying to wrap my head around knowing what method to use in which situation. I know how to write triggers, views, CTEs, stored procedures.
And yes, I realize it may not be ideal to do this, but, putting that aside, I need to know how to do it.
I want to update a parent table with the sum of values from a detail table. I want this because I would like to easily grab the figure, without having to access a view or stored procedure.
In the example of the included code, I'd like the Performance Gross column of the Performance table to be the sum of the PerformanceValues from the performanceDetail table (grouped by performanceID) ; in the sample data, the value would be 75
Option1: create a trigger to run after the detail table is affected by an insert, update or delete; this slows things down, and messes me up if I've done any batch update on the detail table
Option 2: create a stored procedure and run it periodically, to go thru and update all the rows in the parent table
quote:
CREATE TABLE [dbo].[Performance](
[PerformanceDate] [date] NOT NULL,
[PerformanceGross] [money] NULL,
[PerformanceID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Performance] PRIMARY KEY CLUSTERED
(
[PerformanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PerformanceDetail](
[PerformanceID] [int] NOT NULL,
[PerformanceValue] [money] NOT NULL,
[PerformanceDetailID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_PerformanceDetail] PRIMARY KEY CLUSTERED
(
[PerformanceDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PerformanceDetail] WITH CHECK ADD CONSTRAINT [FK_PerformanceDetail_Performance] FOREIGN KEY([PerformanceID])
REFERENCES [dbo].[Performance] ([PerformanceID])
GO
ALTER TABLE [dbo].[PerformanceDetail] CHECK CONSTRAINT [FK_PerformanceDetail_Performance]
GO
insert into Performance (performanceDate) select '3/1/2011'
insert into performancedetail (performanceid,performancevalue) select 1,50
insert into performancedetail (performanceid,performancevalue) select 1,25
Marion in NY |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/20/2013 : 18:13:01
|
I think in the code itself based on PerformanceId when you make insert into PerformanceDetails,can't you update back Performance table with this:
Update P
Set P.PerformanceGross = PerfValue
from Performance P
inner join
(
Select PerformanceId,SUM(PerformanceValue)PerfValue
from PerformanceDetails
Group by PerformanceId
)PFV on PFV.PerformanceId = P.PerformanceId
Where P.PerformanceId = @PerformanceId |
 |
|
|
James K
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 01/20/2013 : 18:23:04
|
I don't like the trigger for the reasons you listed; I don't like stored procedure because it can run only periodically. Unless the nature of your data retrieval operations is such that it can be scheduled carefully when there are no inserts/updates to the table, you may get incorrect values for the aggregates.
You said you don't want to use views, but this may be a case where INDEXED views can benefit you, especially so if you are on the enterprise edition. The nice thing about indexed views that contain aggregates is that even if your query does not explicitly query against the indexed view, it will consider the indexed view while optimizing. That feature is automatically available only on Enterprise edition. (On other editions, you have to explicitly use NOEXPAND hint on your query)
|
|
|
|
MarionT
Starting Member
USA
2 Posts |
Posted - 01/20/2013 : 20:20:55
|
@sodeep - good point, I can integrate code so that when I do inserts/updates to the detail table, I update the parent table simultaneously; that's certainly straightforward. Complication is that I'm trying to build a front end UI using a framework, and so I need to learn how to do the update on the parent table when I do the insert/update on the child table.
@JamesK - I'll look into indexed views; I'm not familiar with that, or with NOEXPAND. I suspect I'm not on an enterprise edition. I'll investigate
Marion in NY |
|
|
| |
Topic |
|
|
|
Trigger or Stored Procedure or ???
