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|
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|
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.