Triggers Need to Handle Multiple Records

On the StackOverflow podcast this week, I caught the guys talking about a SQL Server trigger question and I immediately panicked.  I think nine out of ten times when I see a trigger, it’s written incorrectly, and sure enough, this one had dangerously incorrect answers.

Triggers: You’re Doing It Wrong

When writing a trigger, it’s easy to think in terms of individual records being inserted, updated or deleted.  Developers and DBAs say to themselves, “I’m going to write a trigger to handle the record that just got inserted.”  They write triggers like this below one, which is supposed to only insert records if SomeNumericField is actually numeric:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  DECLARE @isnum TINYINT;
  SELECT @isnum = ISNUMERIC(SomeNumericField) FROM inserted;
  IF (@isnum = 1)
    INSERT INTO sometable SELECT * FROM inserted;
  ELSE
    RAISERROR('SomeNumericField must be numeric', 16, 1)
      WITH SETERROR;
END

Examine that row in bold.  What will happen in the trigger if we insert multiple records simultaneously, like if we run the following T-SQL statement?

INSERT INTO sometable SELECT * FROM someothertable

And what if some rows are valid, and some rows are not?  Will we be able to predict the ISNUMERIC(SomeNumericField) result?  Probably not.  We can rewrite this two ways: either letting the good rows in, or not letting any rows in at all.

Trigger that Rejects Only the Bad Rows

Let’s rewrite it so that we insert the good rows, but reject the bad ones.  I’m just using pseudocode here – you would want to clean this up for production:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  --First we insert the good rows
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 1

  --Then we check to see if we had any bad rows
  IF EXISTS (SELECT TOP 1 * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 0)
    RAISERROR('SomeNumericField must be numeric.  Some rows were not inserted.', 16, 1)
      WITH SETERROR;
END

So this trigger inserted our good rows where SomeNumericField is numeric, and then passed a message back to our code if any records were not inserted.  This is kinda dangerous, because our application won’t know which rows succeeded and which ones failed.  Ideally, we’d track which ones failed, so let’s add code to handle that:

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  --First we insert the good rows
  INSERT INTO sometable SELECT * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 1

  --Then we insert the bad rows into a tracking table
  INSERT INTO someRejectTable SELECT * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 0

  --Then we check to see if we had any bad rows
  IF EXISTS (SELECT TOP 1 * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 0)
    RAISERROR('SomeNumericField must be numeric.  Some rows were not inserted.', 16, 1)
      WITH SETERROR;
END

When you design someRejectTable, all of the fields should be nvarchars, because we can’t predict what kind of data we’re going to be inserting.  After all, the whole reason we’re dumping data into here is because SomeNumericField, which was supposed to be numeric, isn’t actually numeric.

This technique works well for data warehouses where we bring data in from text files and we can’t predict what will come in.  Sometimes people change input file formats, and what used to be a numeric field now has text in it – item numbers are a great example.

Trigger that Rejects Everything if One Row is Bad

If we need to know absolutely everything is okay before we insert our data, here’s how to do it (again, this is pseudocode):

CREATE TRIGGER mytrigger ON sometable
INSTEAD OF INSERT
AS BEGIN
  --Firsts we check to see if we had any bad rows
  IF EXISTS (SELECT TOP 1 * FROM inserted WHERE ISNUMERIC(SomeNumericField) = 0)
    RAISERROR('SomeNumericField must be numeric.  Nothing was inserted.', 16, 1)
      WITH SETERROR;
  ELSE
    --We insert everything, since we know it's all good
    INSERT INTO sometable SELECT * FROM inserted

END

In this code, we check all of the rows first, then if it succeeds, we insert the data.

More Reading on Trigger Development

If this stuff comes as a surprise to you, don’t panic: there’s a really good resource on getting started with T-SQL programming that covers this important issue and a lot of other gotchas.  Check out Beginning SQL Server 2005 Programming by Robert Vieira.  You can preview it in Google Books, and buy it from Amazon.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

Website - Twitter - Facebook - More Posts