SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trigger or Stored Procedure or ???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MarionT
Starting Member

USA
2 Posts

Posted - 01/20/2013 :  16:58:01  Show Profile  Reply with Quote
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
7174 Posts

Posted - 01/20/2013 :  18:13:01  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 01/20/2013 :  18:23:04  Show Profile  Reply with Quote
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)
Go to Top of Page

MarionT
Starting Member

USA
2 Posts

Posted - 01/20/2013 :  20:20:55  Show Profile  Reply with Quote
@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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000