Example: Nested triggers

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:
a. By department and date
b. 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.

How to optimize database triggers

 Home: Introduction
 Part 1: Why use triggers?
 Part 2: How to design and implement triggers
 Part 3: Example: INSTEAD OF triggers versus AFTER triggers
 Part 4: Example: One trigger versus two for the same logic
 Part 5: Example: Nested triggers
 Part 6: Example: Heavy insertion process from OLTP and triggers
 Part 7: Example: Cursors in triggers

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. 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 database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more. Copyright 2006 TechTarget

More from SearchSQLServer.com