Wise man or Wise guy? You Decide

Just another WordPress.com weblog

Triggers in SQL Server

Posted by sqlwiseguy on August 28, 2008

As I was browsing the active threads on SQLServerCentral today I happened on this post about a problem with a trigger. I did not post on the thread because someone had already made the point I would have made. The problem was that the poster had created a trigger that would not work on a batch insert or update. Something like this:

CREATE TRIGGER test_ins_upd ON dbo.test
After
INSERT, UPDATE
AS

DECLARE @test_id INT,
@test_name VARCHAR(25)

SELECT
@test_id = test_id,
@test_name = test_name
FROM
inserted

IF @test_name LIKE 'a%'
BEGIN
--Do something
END
ELSE
BEGIN
--Do something else
END

I 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?

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: