In yesterday’s blog post about using triggers to replace computed columns, a lively debate ensued in the comments. A reader posted a trigger that I should use, and I pointed out that their trigger had a bug – and then lots of users replied in saying, “What bug?”
I’ll demonstrate.
We’ll take the Users table in the Stack Overflow database and say the business wants to implement a rule. If someone updates their Location to a new place, we’re going to reset their Reputation back to 0 points.
Here’s the trigger we’ll write:
1234567891011 | CREATE OR ALTER TRIGGER dbo.ResetReputation ON dbo.Users AFTER INSERT, UPDATE ASBEGIN /* If they moved locations, reset their reputation. */ IF UPDATE([Location]) UPDATE u SET Reputation = 0 FROM dbo.Users u INNER JOIN inserted i ON u.Id = i.Id;ENDGO |
That trigger is completely broken because it doesn’t handle multi-row updates correctly.
To see what I mean, let’s look at all of the users named Brent:
12 | SELECT Id, DisplayName, Location, Reputation FROM dbo.Users WHERE DisplayName = 'Brent'; |
Some of them have locations set, and some don’t:
Let’s update the ones with no location, and set it to be ‘Miami’ – it’s a nice place, after all:
123 | UPDATE dbo.UsersSET Location = COALESCE(Location, 'Miami')WHERE DisplayName = 'Brent'; |
And now check their location & reputation again:
UH OH. Everyone’s Reputation was reset, even people who didn’t change Locations.
Now, you might be saying, “That’s because you changed the Location using a COALESCE.” Nope – let’s check Richies:
And use a different UPDATE;
123 | UPDATE dbo.UsersSET Location = CASE WHEN Location IS NULL THEN 'Miami' ELSE Location ENDWHERE DisplayName = 'Richie'; |
And they all get reset:
You can’t just blindly use UPDATE().
Because sooner or later, somebody’s going to do a multi-row update that affects some of the result set and not others, and your trigger will hit everything in the inserted table.
It’s up to you to figure out specifically which rows you need to process, and what you need to do to ’em.
If you think the query is the problem, watch this module of Mastering Query Tuning where I give an example of a good query, properly written, that *has* to work this way, and the trigger will fail.