Example: Cursors in triggers

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)

How to optimize database triggers

 Home: Introduction
 Part 1: Why use triggers?
 Part 2: How to design and implement triggers
 Part 3: Example: INSTEAD OF triggers versus AFTER triggers
 Part 4: Example: One trigger versus two for the same logic
 Part 5: Example: Nested triggers
 Part 6: Example: Heavy insertion process from OLTP and triggers
 Part 7: Example: Cursors in triggers

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. 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 database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more. Copyright 2006 TechTarget

More from SearchSQLServer.com