Using Triggers - Aggregates
(Page 3 of 4 )
Imagine that users of an Asset5 database are often clogging the Inventory table. One operation that they perform frequently is the execution of reports that prepare the sum of all monthly lease payments per lease schedule. If the sum were prepared in advance, the report would be available in an instant, the table would be less clogged, and the user would experience fewer locking and deadlocking problems.
To provide this functionality, you could create one or more triggers to maintain the PeriodicTotalAmount field in the LeaseSchedule table. The field will contain the sum of lease payments for assets in the Inventory table that are associated with a lease schedule.
It is possible to implement diverse solutions for this task. This solution is based on separate triggers for different modification statements.
The Insert trigger is based on a relatively complex Update statement with a subquery based on the contents of the Inserted table. Each new record increments the total in the related lease schedule.
The Coalesce statement is used to replace nulls with zeros in the calculation. The trigger evaluates the number of records affected by the modification statement at the beginning and, if no records are affected, aborts further execution.
This solution executes properly even when multiple records are inserted with one statement:
Create Trigger dbo.trInventory_Lease_I
On dbo.Inventory
after Insert -- For Insert
As
If @@Rowcount = 0
return
-- add inserted leases to total amount Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
LeaseSchedule.PeriodicTotalAmount
+ Coalesce(i.Lease, 0)
from dbo.LeaseSchedule LeaseSchedule
inner join inserted i
on LeaseSchedule.ScheduleId = i.LeaseScheduleId
Go
The Delete trigger is very similar to the previous trigger. The main difference is that the values from the Deleted table are subtracted from the total, as shown here:
Create Trigger dbo.trInventory_Lease_D
On dbo.Inventory
After Delete -- For delete
As
If @@Rowcount = 0
Return
-- subtract deleted leases from total amount
Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
LeaseSchedule.PeriodicTotalAmount
- Coalesce(d.Lease, 0)
from dbo.LeaseSchedule LeaseSchedule
inner join deleted d
on LeaseSchedule.ScheduleId = d.LeaseScheduleId
Go
The Update trigger is the most complicated. The calculation of a total is performed only if the Lease and LeaseScheduleId fields are referenced by the Update statement. The trigger then subtracts the Lease amounts from the deleted records and adds the Lease amounts from the inserted records to the related totals:
Create Trigger dbo.trInventory_Lease_U
On dbo.Inventory
After Update -- For Update
As
if @@Rowcount = 0
return
If Update (Lease) or Update(LeaseScheduleId)
begin
-- subtract deleted leases from total amount
Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
LeaseSchedule.PeriodicTotalAmount
- Coalesce(d.Lease, 0)
From dbo.LeaseSchedule LeaseSchedule
inner join deleted d
On LeaseSchedule.ScheduleId = d.LeaseScheduleId
-- add inserted leases to total amount
Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
LeaseSchedule.PeriodicTotalAmount
+ Coalesce(i.Lease, 0)
From dbo.LeaseSchedule LeaseSchedule
inner join inserted i
On LeaseSchedule.ScheduleId = i.LeaseScheduleId
End
Go
Next: Enforce Schema Integrity Among Objects on Different Servers or Databases >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 5 
