Why triggers sometimes suck

We took our annual trip Up North this last week. My son really wanted to get his first walleye this year, and I did everything I could to make it happen. The lake we were on is certainly not a “numbers” lake for any species except dinky bluegill. However, our second-to-last night we did manage to hook up with a great 24″ walleye. In many ways, seeing the thrill on kids’ faces as they catch a big fish is even more fun that catching the fish yourself.
Nate Walleye

Triggers are an extremely useful tool in SQL Server. However, I mostly hate them. Like any tool, they have a proper place and are quite effective when used properly. Unfortunately, in actual use they are more often than not used improperly.

Homer Fixes Camera

Tools used improperly

I’m not saying many third party databases have been created by Homer Simpson, but for some of them, it wouldn’t surprise me.  I came across this “interesting” setup while investigating a deadlocking issue on a third party vendor’s database:

CREATE TABLE [cwi].[dwtran]
(
    [ibcomp] [DECIMAL](3, 0) NOT NULL
  , [ibponr] [DECIMAL](12, 0) NOT NULL
  , [ibe2vn] [VARCHAR](3) NOT NULL
  , [ibkggm] [VARCHAR](80) NOT NULL
  , [ibhody] [DECIMAL](7, 0) NOT NULL
  , [ibectm] [DECIMAL](6, 0) NOT NULL
  , [ibamry] [VARCHAR](1) NOT NULL
  , [ibhpdy] [DECIMAL](7, 0) NOT NULL
  , [ibedtm] [DECIMAL](6, 0) NOT NULL
  , [ibhqdy] [DECIMAL](7, 0) NOT NULL
  , [ibeetm] [DECIMAL](6, 0) NOT NULL
  , [ibkncf] [VARCHAR](10) NOT NULL
  , [id] [INT] IDENTITY(1, 1) NOT NULL
) ON [PRIMARY];
GO

CREATE TRIGGER [cwi].[assign_key]
ON cwi.dwtran
AFTER INSERT
AS
BEGIN
    UPDATE dw
    SET dw.ibponr = id
    FROM cwi.dwtran dw
        INNER JOIN Inserted i ON i.id = dw.id;
END;
GO

What is this trigger doing? It’s grabbing the id identity value for the row that just got inserted and setting the ibponr column to the exact same value.  This setup would probably work ok on a system that always accesses the table serially.  However, this table is a change history table that captures all transactions in a busy order management system for insertion into a data warehouse.  There are several application servers accessing the database at the same time.  The trigger was deadlocking with subsequent INSERTS causing transactions to fail.  Additionally, each insert into the table also required an update, effectively costing two transactions for a single insert.

In this case, since the incremental data warehouse load only ran once per day, I was able to create a SQL Agent job that update the missing ibponr records in a single update.  I ran the SQL Agent job right before the data warehouse load.  In a perfect world, the data warehouse load (which is run by the application, not in the database) would update those ibponr values as its first step.

Another reason I dislike triggers is that when they cause a rollback, the error can be vague and the reason for the rollback is often hard to find.

One last thing to keep in mind from a development perspective is that triggers have to be written from the perspective that multiple rows can be inserted simultaneously.  The trigger I showed above assumes that only one row will be inserted at a time, but that isn’t always the case!

Advertisements