If Your Trigger Uses UPDATE(), It’s Probably Broken.

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:

CREATE OR ALTER TRIGGER dbo.ResetReputation ON dbo.Users AFTER