Trigger or Stored Procedure or ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger or Stored Procedure or ???

Author

Topic

MarionT
Starting Member

2 Posts

Posted - 2013-01-20 : 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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-20 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-20 : 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

2 Posts

Posted - 2013-01-20 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources