T-SQL & Development
[Video] You shouldn’t dynamically create/alter/drop triggers.
Let’s get one thing out of the way first: this isn’t about the relative good or evil of triggers. I kinda like triggers, in fact – they’re good for enforcing business logic in a hurry when you can’t redo code across an entire application. Sure, in a perfect world, we’d always have the code do whatever the code needs to do – but there are times when you can’t, and triggers come in handy then.
Just as you need to be judicious about what you do inside a trigger, you also need to be careful about when you actually deploy one, and you should probably never dynamically create/alter/drop a trigger. See, when you create, alter, or drop a trigger, you’re changing the table’s structure, which requires a higher level of locking than you might expect.
To demonstrate it, I’m going to create a trigger on the Votes table in the Stack Overflow database at the same time end user activity is happening, and show the blocking:
Here’s the demo code that I use in the video:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT COUNT(DISTINCT Id), COUNT(DISTINCT PostId), COUNT(DISTINCT UserId), COUNT(DISTINCT BountyAmount), COUNT(DISTINCT VoteTypeId), COUNT(DISTINCT CreationDate) FROM dbo.Votes; GO CREATE OR ALTER TRIGGER dbo.Meaningless ON dbo.Votes FOR UPDATE AS SELECT GETDATE(); GO SELECT TOP 1 * FROM dbo.Votes WITH (NOLOCK); |
So what should you do instead? Treat trigger changes just like any other kind of deployment of a table change, like adding a column or dropping a column:
- Try to schedule it off-hours during a low load period.
- Consider the blocking implications of changing the object.
- Only do it when there’s a human being around to watch what’s happening, and cancel the deployment if it’s causing a huge disruption.
- And maybe most obviously, only do it once. Don’t automate the same change over and over if you can avoid it – it’s just too disruptive.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Is the table locking behavior the same for enable/disable on triggers as well?
Phil – yep!
I can only imagine the events that took place that prompted you to write this one, Brent. 😀 I never even considered doing such a thing.
Jeff – hahaha, yeah, I’ve actually run into it a few times! Turns out there’s an open source product that does this.
Good lord. Pure genius.
Given the locking nature of altering triggers, are you suggesting applying the changes during “low usage” times, or scheduling maintenance windows to ensure no activity, or???
Kevon – go ahead and reread the last paragraph.
/facepalm
I watched the video and assumed all comments were covered in it. Thanks.
I learned this the hard way a couple of years ago. Deployed an update trigger on a busy table (took a few attempts using a low priority and short lock timeout). However once it eventually deployed it fell into a bad plan and 4ms updates turned into 5,000ms or higher. Active sessions went from ~20 to ~1000. 32 CPU cores maxed out. Customer traffic was still being served, but latency was massive. Every time I tried to drop the trigger it would block everything writing to the table. After 30 seconds of waiting, customers would be getting timeouts, so I couldn’t let it wait for too long.
Our server ran at 100% for 5 hours until load naturally reduced at the end of the day – enough to eventually let us drop the trigger.
The trigger is back in live, this time with query hints to prevent the bad plan and a circuit-breaker (checking for the presence of a global temp table) to allow us to stop the trigger doing anything if it ever goes bad in future.
BWAAAA-HAAAA!!!! Nice trick to “prove” that “we need more hardware NOW”. 😀