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:
SELECT COUNT(DISTINCT Id),
CREATE OR ALTER TRIGGER
dbo.Meaningless ON dbo.Votes
FOR UPDATE AS
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.