Best Practice: coding SQL Server triggers for multi-row operations
There are many forum posts where people code triggers but these triggers are coded incorrectly because they don’t account for multi-row operations. A trigger fires per batch not per row, if you are lucky you will get an error…if you are not lucky you will not get an error but it might take a while before you notice that you are missing a whole bunch of data
Let’s take a look, first create these two tables
create table Test(id int identity not null primary key,
SomeDate datetime not null)
GO
create table TestHistory(id int not null,
InsertedDate datetime not null)
GO
Now create this trigger, this trigger is very simple, it basically inserts a row into the history table every time an insert happens in the test table
CREATE TRIGGER trTest
ON Test
FOR INSERT
AS
IF @@ROWCOUNT =0
RETURN
DECLARE @id int
SET @id = (SELECT id
FROM inserted)
INSERT TestHistory (id,InsertedDate)
SELECT @id, getdate()
GO
Run this insert statement which only inserts one row
insert Test(SomeDate) values(getdate())
Now run this to see what is in the history table
select * from TestHistory
1 2009-11-12 14:51:21.103
That all works fine, what happens when we try to insert 2 rows?
insert Test(SomeDate)
select getdate()
union all
select getdate() + 1
Here is the error.
_Server: Msg 512, Level 16, State 1, Procedure trTest, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated._
What would happen if you coded the trigger in this way
ALTER TRIGGER trTest
ON Test
FOR INSERT
AS
IF @@ROWCOUNT =0
RETURN
DECLARE @id int
SELECT @id = id
FROM inserted
INSERT TestHistory (id,InsertedDate)
SELECT @id, getdate()
GO
Now insert one row
insert Test(SomeDate) values(getdate())
We look again what is in the history table, as you can see we have id 1 and 4, this is because id 2 and 3 failed and were rolled back
select * from TestHistory
1 2009-11-12 14:51:21.103 4 2009-11-12 14:52:08.370
Here is where it gets interesting, run this code
insert Test(SomeDate)
select getdate()
union all
select getdate() + 1
That runs fine but when we look now we are missing row 5 in the history table
select * from TestHistory
1 2009-11-12 14:51:21.103 4 2009-11-12 14:52:08.370 6 2009-11-12 14:52:20.167
let’s try that again
insert Test(SomeDate)
select getdate()
union all
select getdate() + 1
Now we are missing row 7 in the history table
select * from TestHistory
1 2009-11-12 14:51:21.103 4 2009-11-12 14:52:08.370 6 2009-11-12 14:52:20.167 8 2009-11-12 14:52:38.917
The problem is with this line of code
SELECT @id = id FROM inserted
@id will only hold the value for the row that was returned last in the result set
Here is how you would change the trigger to work correctly
ALTER TRIGGER trTest
ON Test
FOR INSERT
AS
IF @@ROWCOUNT =0
RETURN
INSERT TestHistory (id,InsertedDate)
SELECT id, getdate()
FROM inserted
GO
Now run this
insert Test(SomeDate) values(getdate())
We can now verify that it works correctly
select * from TestHistory
1 2009-11-12 14:51:21.103 4 2009-11-12 14:52:08.370 6 2009-11-12 14:52:20.167 8 2009-11-12 14:52:38.917 9 2009-11-12 14:53:44.433
Now run this for 2 rows
insert Test(SomeDate)
select getdate()
union all
select getdate() + 1
And as you can see both rows were inserted into the history table
select * from TestHistory
1 2009-11-12 14:51:21.103 4 2009-11-12 14:52:08.370 6 2009-11-12 14:52:20.167 8 2009-11-12 14:52:38.917 9 2009-11-12 14:53:44.433 10 2009-11-12 14:53:51.870 11 2009-11-12 14:53:51.870
So what is worse in this case? The error message or the fact that the code didn’t blow up but that the insert wasn’t working correctly? I’ll take an error message any time over the other problem.
I am putting together a SQL Server Best Programming Practices wiki page, this blog post is part of it as are other posts and articles either from this site as well as from other sites. I am still working on the SQL Server Best Programming Practices wiki page but I encourage you to bookmark it and come back every now and then because I will be adding more content
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum