This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happiness as 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 how I first thought of them after reading the post. If you think I’ve mischaracterized one, let me know.
In case you don’t want to add, it’s 9 to 13, so triggers are a headache.
Helpful Triggers
I have to start with Burt Wagner, who gets top billing while wearing a Hawaiian Shirt. Thanks, Burt, and interesting solution. In the case where Burt wants to use Temporal Tables in pre-SQL 2016 instances, he uses triggers to manage the history table. I think that’s a great way to use triggers, and while slightly hidden, this should overall work well. The downside is that any schema changes to the table need changes to the trigger and history table, which could be slightly lost if developers don’t realize this.
The newest evangelist at Redgate is Kendra Little, who loves triggers. Her post looks at her first experience with triggers. It was positive as a way to detect changes for downstream systems.
Shane O’Neil has written about when triggers are good, talking about the way in which he tracks when new databases appear on his instance, with an email to auditing.
Aaron Bertrand discusses INSTEAD OF triggers, and gives you the places where these are useful, or even necessary.
I’ll file this as helpful when Peter Schott writes some code to disable triggers.
Data migration is always tricky, but Jay Robinson gives a way in which triggers are helpful. I think that this is a great use, and more people ought to do this to simplify deployments and let data migrations occur over time, not all at once.
Despite his misgivings, I think Marek Masko shows that he uses triggers effectively in code.
I think Service Broken is amazing, and I wish that messaging and queueing processes were a pattern more of us used. Reid DeWolfe shows how triggers work in conjunction with Service Broker to get things done.
A simple solution from Eugene Meidinger with the cautions that we need to be very careful with server level triggers.
Headache Triggers
Rob Farley has a discussion about some of the problems you can run into with triggers. He likes them, and has written about them before, but he offers advice for how to deal and write triggers in your organization.
Dave Mason has a post on DDL triggers, which fire in response to event classes. If you’ve never used these, Dave gives you a few ideas on where they might be handy.
We get a look at the good, bad, and ugly of a trigger in an AG from Bob Pusateri.
David Fowler has a great post on nested triggers, with Service Broker in between to ensure there is no limit to the number of times the trigger fires.
Jon Shaulis has a long post that provides an overview of triggers, where they work and don’t, and includes a few ideas for replacing triggers.
Claudio Silva writes about triggers being hidden and wasting his time.
I almost can’t believe the number of triggers Allen White has seen on a table, but since I have had similar customers and employers, I’m not surprised.
Logon triggers can be headaches, and James Livingston shares one of his with us.
The great Hugo Kornelis gives us a way to use triggers effectively and not be sad. Vote for a suggestion to make them more visible in SSMS. I voted, and I agree. We need better visibility.
While Eduardo Pivaral writes about good and bad triggers, I think he feels triggers are somewhat bad overall. Certainly his “good fix” might not have been the best choice in his eyes, even though it worked.
Not realizing that a trigger needs to operate on a number of records is a common mistake, so I’m putting Brian Dudley’s post in this area.
I’d say that trigger anti-patterns fall into the headache area, and we get a few situations from Nate Johnson.
Matthew McGiffen tells about the trouble with triggers. As with many of the other posts, he finds that triggers might be worth less than we initially think.
Voice of the DBA 