Why did we choose to use triggers to update the balance table?
- Consistency between transaction table and balance table.
- High performance due to the efficiency of SQL Server triggers
- Apart from the triggers themselves, no additional code is needed to keep the balance table up to date.
- Chance of locking errors when 2 transactions update different transaction records, which related to the same balance record.
- Define 3 triggers: One for insert, one for update and one for delete. One trigger for update, insert and delete resulted in our solution to locking errors.
- Start every trigger with the statement IF @@ROWCOUNT = 0 RETURN. This is done for performance reasons, if no inserts, updates or deletes took place at all, do not do anything.
- To limit locking situations:
1) Keep database transactions as short as possible.
2) Do not delete the balance record if all underlying transactions are deleted. In most situations this balance record will be used when new transactions are created.
- In the past we have used indexed views, however because of performance reasons we moved to the trigger solution.