XML Feeds
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.