Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server trigger example: Cursors in SQL Server triggers

This example shows how cursors in triggers could degrade Microsoft SQL Server performance, and provides an alternative method.

Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Problem: You have a table that contains queued requests from your application. Every hour, a scheduled job runs and updates Second Table with all pending requests with a complicated logic.

Second Table can be updated from other processes, such as the OLTP application, and only inserts can be invoked to it. No updates. You must summarize the data inserted in Second Table in Third Table no matter which process issued the insert. You would like this logic to be handled by a trigger for consistency and transparency.

The summary in the Third Table is complex, as follows:

Data inserted to Second Table will look like this:

 

Amount Date From Amount To Amount Amount Type (Each process that inserts data into table has its own type
2000-01-01 10 100 1
2000-01-02 100 200 1
2000-01-05 200 300 1
2000-01-08 320 400 1
2000-01-09 350 400 1

Date and Amount Type make up the Primary Key of Second Table. Data is inserted sequentially for each Amount Type.

The following data has to be inserted to Third Table.

 

Amount Type From Date To Date  Gap = From Amount (Date2) – To Amount (Date1) where Gap <> 0
1 2000-01-05 2000-01-08 20
1 2000-01-08 2000-01-09 -50

Important: The last row inserted to Second Table before the current execution must be compared with first row currently inserted.

Solution: You first think to use a cursor, right? If you have used cursors within your triggers (especially when inserting many rows), you probably perceived performance degradation. Please take a look at my alternative solution:

 

 Create Trigger trg_I_SecondTable on SecondTable For INSERT As If @@ rowcount = 0 return -- save the amount type: Declare @AmountType tinyint Select top 1 @AmountType = AmountType from inserted -- Create a temporary table with identity column for later -- joining it to itself with prior identity (note that I order -- the rows in the temporary table by the date). Select id = identity(int,1,1), * into #tmp From inserted Order by AmountDate -- Insert the correspond results from inserted Insert into ThirdTable (AmountType, FromDate, ToDate, Gap) Select t1.AmountType, t1.AmountDate, t2.AmountDate, t2.FromAmount-t1.ToAmount From #tmp t1 inner join #tmp t2 on t1.id = t2.id-1 Where t2.FromAmount-t1.ToAmount <> 0 union all -- Insert the comparison between the first inserted row to the last -- row in the SecondTable (with the same AmountType): select t1.AmountType, t1.AmountDate, t2.AmountDate, t2.FromAmount-t1.ToAmount From SecondTable t1 inner join #tmp t2 on t2.id = 1 where t1.AmountType = @AmountType and t2.FromAmount-t1.ToAmount <> 0 And t1.AmountDate = (select max(t3.AmountDate) from SecondTable t3 where t3.AmountType = @AmountType)

Explore Microsoft SQL Server triggers

ABOUT THE AUTHOR:
Michelle Gutzait works as a team member of the SQL Server Database Experts at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.

This was last published in May 2006

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close