I don’t have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you’re not allowed to change the application. As long as you keep the number of statements to a minimum (say, 2-3), and don’t try to do something really slow like fire up a cursor, triggers can be an efficient way to solve hard problems quickly.

However, most triggers I run across have a really, really dangerous bug.

Let’s say we want to add a trigger on the Stack Overflow database‘s Users table. Whenever someone’s Reputation is over 1,000 points, we’re going to set their AboutMe to declare that they’re famous:

CREATE OR ALTER TRIGGER trUserIsFamous ON dbo.Users AFTER UPDATE AS BEGIN DECLARE @Id INT, @Reputation INT; SELECT @Id = Id, @Reputation = Reputation FROM INSERTED; IF @Reputation > 1000 UPDATE dbo.Users SET AboutMe = 'FAMOUS FOR A HIGH REPUTATION!' FROM dbo.Users u WHERE Id = @Id; END GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE OR ALTER TRIGGER trUserIsFamous ON dbo . Users AFTER UPDATE AS BEGIN DECLARE @ Id INT , @ Reputation INT ; SELECT @ Id = Id , @ Reputation = Reputation FROM INSERTED ; IF @ Reputation > 1000 UPDATE dbo . Users SET AboutMe = 'FAMOUS FOR A HIGH REPUTATION!' FROM dbo . Users u WHERE Id = @ Id ; END GO

That trigger assumes only one row will be in the INSERTED table, which would be true – as long as we only update one row at a time. But what if a query updates multiple rows in a single transaction?

Let’s say we want to award a free reputation point to everyone in Brazil. Here are some of the people we’re going to affect:

SELECT TOP 20 * FROM dbo.Users WHERE Location = 'Brazil' ORDER BY Reputation DESC; 1 2 3 4 SELECT TOP 20 * FROM dbo . Users WHERE Location = 'Brazil' ORDER BY Reputation DESC ;

There are lots of people involved:

Many of them have reputations over 1,000 points, so those folks are all going to be set to famous, right? Let’s see what happens when we run the update:

And then check to see their new famous AboutMe:

Wait…something went wrong. There are users with a reputation over 1,000, but don’t have “FAMOUS” in their AboutMe. A bunch of ’em simply got ignored.

That’s the bug.

When you declare variables and set them using one row from the INSERTED or DELETED virtual table, you have no idea which row you’re going to get. Even worse, sometimes this trigger will update one row, and sometimes it won’t – because it might happen to grab a row with a reputation under 1,000!

Here’s a better way to write that trigger.

Don’t use variables – instead, think set-based:

CREATE OR ALTER TRIGGER trUserIsFamous ON dbo.Users AFTER UPDATE AS BEGIN UPDATE dbo.Users SET AboutMe = 'FAMOUS FOR A HIGH REPUTATION!' FROM dbo.Users u INNER JOIN INSERTED i ON u.Id = i.Id WHERE i.Reputation > 1000; END GO 1 2 3 4 5 6 7 8 9 10 11 CREATE OR ALTER TRIGGER trUserIsFamous ON dbo . Users AFTER UPDATE AS BEGIN UPDATE dbo . Users SET AboutMe = 'FAMOUS FOR A HIGH REPUTATION!' FROM dbo . Users u INNER JOIN INSERTED i ON u . Id = i . Id WHERE i . Reputation > 1000 ; END GO

In that version, we’re simply joining with the INSERTED table, thereby ensuring that we get all of the updated rows. We operate on ’em all at once, too – think set-based, not a cursor.

Then, when we update multiple rows at once:

The right ones are affected:

tl;dr: when you see the variables being set with the contents of a single row from the DELETED and INSERTED tables, you need to raise an urgent alarm because that trigger probably produces incorrect results when multiple rows are deleted/updated/inserted in a single statement.

“Hey, that’s not SSMS in those pictures.”

Yeah, you caught me: I’m using Azure Data Studio on my Mac, and building SQL notebooks with it. Here’s a quick runthrough of how it works:

How to get started: