CREATE TRIGGER test_ins_upd ON dbo.testI seem to see this mistake on the majority of forum posts about triggers. SQL is a set-based language and triggers deal with sets as well. I have to admit that when I was new to SQL Server I made the same mistake, but only once. I had thought that since we only allowed access to the database through stored procedures, and these procedures only acted on 1 row that I would be safe. Well, I quickly learned I was wrong. What about what I did? Or, another developer?
After INSERT, UPDATE
DECLARE @test_id INT,
@test_id = test_id,
@test_name = test_name
IF @test_name LIKE 'a%'
--Do something else
The first thing a new person to SQL needs to learn and really understand is that their code needs to work with sets. This will help newbies to write better performing code and help them avoid errors like this one.
Here are a couple of good blog posts about triggers:
The Trouble with Triggers by Conor Cunningham
Triggers...Evil? by Louis Davidson