The Benefits Of DML Triggers

Kendra Little tells us a tale of trigger value:

When I first edged my way into a Junior DBA-ish role, I worked with a complex application with many large databases. Customers loaded and configured data into a (mostly) OLTP style database, and then database was replicated to multiple other systems — some to publish data to and adserving platform, and some to transform the data for reporting.

Triggers were used extensively in these downstream systems to detect changes. It went like this:

  • Transactional replication publications were set up on the OLTP  (ish) database.

  • Transactional replication subscriptions were set up on each downstream server. A dedicated database was used for replication articles on each instance.

  • After replication was initialized, DML triggers were created on each article in the subscriber database. For each modification, the trigger would insert a narrow row into a “delta” table related to that article.

  • The “delta” tables were in their own schema, and contained row identifiers, timestamp columns, and custom indexes for processing. This enabled batches to be efficiently pulled from these tables for processing into the related system.

  • Cleanup processes periodically pulled processed rows out of the delta tables in the background (and indexes were designed to prevent the cleanup process from fighting with data processing jobs and inserts)

Read the whole thing.  There are some things that triggers can do easily which would be difficult to handle otherwise, but they can also be dangerous in the wrong hands.

Related Posts

T-SQL Tuesday Trigger Roundup

Steve Jones has a bunch of links about triggers this month: This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happinessas the topic, and I am glad that there have been quite a few responses. I started the review almost immediately,and here are a few highlights. I separated these based on […]

Read More

Trigger Spirals

David Fowler tells a story of woe, one which is totally not his fault: To do this, a trigger was created which would send all the details via a Service Broker message to another SQL Server, this SQL Server was used to hold details of the AD accounts and from there, changes were automatically propagated […]

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

September 2018
MTWTFSS
« Aug  
 12
3456789
10111213141516
17181920212223
24252627282930