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
Home Introduction
Part 1 Why use triggers in SQL Server?
Part 2 Designing and implementing triggers in SQL Server
Part 3 Example: INSTEAD OF triggers vs. AFTER triggers
Part 4 Example: One trigger vs. two for the same logic
Part 5 Example: Nested triggers in SQL Server
Part 6 Example: Heavy insertion process from OLTP and triggers
Part 7 Example: Cursors in 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. |
|
|
|
|
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
