SQL Server trigger example: Nested triggers in SQL Server

This example shows how to use triggers to summarize data for a table in Microsoft SQL Server that is heavily updated.

Intro | Part 1 | Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7

Problem: You have a table that is heavily updated (about 100 inserts and updates per second) from several applications. You have to summarize data in two levels:

  1. By department and date
  2. By date

Management will view the summarized data online, so data has to be as up to date as possible (a one- to two-minute delay is acceptable).

Solution: We can implement this via triggers:

Now, think about the possible implications of this solution:

  • The updating application waits until all these levels of hierarchy are committed.
  • The transaction length is longer, so rollbacks can take longer, more locks and deadlocks may arise.
  • You will likely have to add more triggers to this hierarchy in the future.

What other ways can you apply this solution? Here are some ideas:

  • Continuous replication: The data will immediately transfer to the destination tables without being part of the applications' transaction.
  • Use timestamps to identify the changed rows: An external process will run every minute or so and propagate the changes to the summary tables.
  • Program a queue: The first trigger can insert rows into the queue and another process can propagate the changes to the other tables.
  • Use the with (nolock) option: If rows are not updated within a transaction or if the application can allow "dirty reads" of the data, consider using the with (nolock) option when querying the base table in order to boost performance.

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

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning