Category: Triggers

Andy Mallon builds a trigger guard:

I recently saw a question on DBA Stack Exchange (it has since been deleted by the author), who had a “special process” that ran regularly, and as part of that process, they disabled the trigger, did some stuff, and re-enabled it. During that process, the step that disables the trigger would deadlock, and cause problems. So the asker was wondering how to catch & handle the deadlock during the DISABLE TRIGGER step.

Yeah, disabling the trigger, not so great. Read on for one interesting way of doing it, as well as a few other methods in the comments.

Chad Callihan shows how you can roll your own change tracking with triggers:

Have you ever come across triggers being used to audit table changes? In this post, we’ll look at a few examples of DML triggers in action.

Click through for examples around tracking updates and last edit times, as well as creating audit tables for operations.

If you do go this route, Chad has an important warning. In addition, I appreciate that the statements in this example support multi-row updates. That’s something people often forget when writing triggers.

Thomas Rushton has a warning for us:

Let’s test that script:

Create Table Trigger Fired
Completion time: 2021-02-27T14:17:09.4783931+00:00

Great. Refresh the tables list in the object explorer to go poking around, and:

Hmm. We have a problem.

Click through to understand what happened.

Ed Pollack tries to improve our usage of triggers:

SQL Server triggers are a subject of both functionality and controversy on any database platform. They can provide immense utility at a low cost, but are often misused by developers, DBAs, and architects alike, resulting in performance bottlenecks or maintainability challenges.

This article briefly reviews DML (data manipulation language) triggers and delves into a discussion about how triggers can be efficiently and effectively used as well as when triggers can dig developers into holes that are hard to escape.

Read the whole thing. Triggers are rather useful tools when used correctly, but it’s easy to misuse them on accident.

Kenneth Fisher has a public service announcement:

While I was helping him with part of it I noticed that the trigger was only set up to handle single row updates. When I told him it needed to be able to handle multiple row updates he asked me in all seriousness, “Why? Since only one value can be changed at a time why should I worry about multiple rows being changed at once?”

Read on for Kenneth’s response, which approximates to Au contraire mon frère. Unhandled assumptions often become bugs.

Louis Davidson walks us through a tricky problem:

Writing data integrity code in TRIGGER objects is generally is pretty simple. Typically you write a query to see “is there a row in inserted/deleted that is not set up as desired for the current transaction?” and no bad data is found, keep moving. And because MOST of the time, you would only use a TRIGGER object where transactions are quick coupled with the reality that a lot of the issues like I will describe would have to occur within milliseconds… concurrency issues seem to rarely occur. But…anything that isn’t impossible, is possible. Because of the rarity of the issue, if your code isn’t running a life of death system, when the anomalies happen, they are brushed off as “just something weird occurred”. 

This blog will cover one such weird occurrence that can happen when your trigger validation code queries another table in the default isolation level. You expect that your data is protected until your transaction ends, but it may not be.

Do read the whole thing.