SQL Server INSTEAD OF Trigger “Gotchas” Revealed

by bob on May 26, 2011

I just finished implementing a schema change on a table that is central to a public-facing database.  The objective was to normalize the table, and this required that the original table be broken down into five tables.  This will allow my client a lot more flexibility, but it also means that hundreds of thousands of lines of existing code would break.  So it was a requirement that existing code would continue to work as before.

The obvious solution was to replace the original table with a view of the same name.

Views can’t, on their own, be updateable against more than one underlying table, and the solution to that is to write INSTEAD OF INSERT, INSTEAD OF UPDATE, and INSTEAD OF DELETE triggers.  These special triggers run whenever an INSERT, UPDATE, or DELETE statement are run and take the place of SQL Server’s default behavior.

I felt pretty good about going ahead with this since the feature is mature, having been around quite a few years now, and I was not seeing wails of agony out there in the cloud from people using it.

No one on our team had experience with INSTEAD OF triggers, but it didn’t seem particularly painful — it was a fair amount of TSQL code, and a fairly involved test plan, but it all seemed to come together fine.

It was no surprise, however, that I came out of this with a couple of unwelcome discoveries. If you’ve Googled your way here, you probably are contemplating a similar project of your own.  I’m about to save you some headaches.  (You’re welcome!)

The first thing they don’t tell you in the trivial, contrived examples that explain INSTEAD OF triggers is that they fire once per SQL statement, not once per record.  In other words, if someone issues, say, an INSERT INTO … SELECT or an UPDATE or DELETE with a WHERE clause that matches more than one record, an INSTEAD OF trigger written with the assumption that it will fire once per record will only impact one of the records in the result set, not all of them.

When a multi-record action is performed, the special Inserted and Deleted tables will contain all the records that are part of the result set, so the “ideal” solution from a performance standpoint is to JOIN all your updates within the trigger to the Inserted or Deleted table, as appropriate.

This isn’t always the simplest and most maintainable code, though; in our case we already had code that was working perfectly for a single-record update, and we just wrapped all that in a loop driven by a cursor.  Although this is supposed to be less performant, we found it perfectly acceptable.  Your mileage may vary.

The second “gotcha” didn’t show itself until we were a couple of days into production with the new schema.  We had a bit of dynamic SQL embedded in an application that did an INSERT into the old table (now a view) and then called SCOPE_IDENTITY() to return the newly-inserted primary key value.

Lo and behold, it turns out that SCOPE_IDENTITY() will not under any circumstances work with a view controlled by an INSTEAD OF INSERT trigger.  The reason: SCOPE_IDENTITY(), as its name suggests, returns the last identity key value INSERTed in the current scope.  Since triggers operate in their own unique scope, which you can’t call into or access, SCOPE_IDENTITY() returns NULL.  Whatever INSERTs were done within the trigger, the outside world can’t know about.  Besides, you could well do multiple INSERTs within an INSTEAD OF INSERT trigger, and how is Sql Server to know which one you’re interested in?

The only solution I could find was to use @@IDENTITY instead.  This is a little jarring, since for years Microsoft has recommended using SCOPE_IDENTITY() as superior to the older @@IDENTITY. By now, using SCOPE_IDENTITY() is almost reflexive for most of us.  Now you have to remember to use @@IDENTITY on views that have INSTEAD OF INSERT triggers — not a good thing! Even then, @@IDENTITY is somewhat fragile as it will fail in certain cases if more than one trigger is in the picture for a given INSERT.  Fortunately for us, we have very few triggers in production in this system, so we got away with it in this situation.

Remember that @@IDENTITY returns the most recently created identity value created in the current connection, so it’s going to return the value from the last INSERT made by your INSTEAD OF INSERT trigger.  In our case, again, we were lucky — this is exactly what we wanted back from the trigger.  I’m sure there are situations where it would be awkward or even impossible to structure the trigger this way, though.

UPDATE: We had to put a new table under management in the INSTEAD OF INSERT trigger that made the above-mentioned INSERT no longer the last one, and there was no other way to do it since the new table needed the identity value returned by SCOPE_IDENTITY() in the prior INSERT. We dodged this bullet by simply not allowing the new table to have its own IDENTITY field; this means that outside the trigger, @@IDENTITY will still return the identity value from the prior INSERT. Fortunately, the new table had a perfectly usable natural primary key of its own based on two fields in its schema. But it’s fragile stuff like this that has us scrambling to undo our dependency on the view!

Others have not been so lucky as we were: for example, if you’re using an entity framework on top of Sql Server that relies on SCOPE_IDENTITY() and it can’t be configured or modified to use some other approach, you are simply out of luck.

What’s missing from the implementation of INSTEAD OF INSERT triggers is a mechanism for setting the value that SCOPE_IDENTITY() will return in the calling scope, or at least some other way to communicate the new primary key for the view record.

So that’s the story on INSTEAD OF triggers, pilgrim: make sure you design these triggers with multiple-record actions in mind, and for INSTEAD OF INSERT triggers, be sure you can live without SCOPE_IDENTITY(), or that you can engineer some way around its absence.

{ 5 comments… read them below or add one }

Jeff Humphreys December 12, 2011 at 9:30 pm

Yep, definitely enjoying INSTEAD OF. I used uniqueidentifier instead of IDENTITY so I’m hosed as far as getting anything back from the trigger. I’m going to try (crazy-time) saving the session id in a table with the new ids. The calling system can fetch those. Not super secure.

Steve February 28, 2012 at 1:37 pm

I was just about to write my first INSTEAD OF INSERT trigger. I’m really glad I read your article and will now plan accordingly. Thanks for making your experience “reusable” for the rest of us.

Ron Ward July 29, 2013 at 6:16 pm

Try putting the new identity value into CONTEXT_INFO and selecting it out after the trigger runs. I haven’t tried it but it seems like it would work.

bob July 29, 2013 at 8:02 pm

Bob says: This looks promising, because of the following in the Transact-SQL docs, at least back to Sql Server 2005:

“When you issue SET CONTEXT_INFO in a stored procedure or trigger, unlike in other SET statements, the new value set for the context information persists after the stored procedure or trigger is completed.”

However it must be retrieved in the same connection or batch. Also, you still have to remember not to use SCOPE_IDENTITY() when issuing an INSERT against a view that happens to have an INSEAD OF INSERT trigger. If you have to remember to use @@IDENTITY, using CONTEXT_INFO() is just another way of doing the same thing. However — this should help with the problem of inflexibility where even @@IDENTITY could return the “wrong” primary key in certain situations. It would provide an explicit way for the trigger to communicate the “right” primary key to the outside world. And it would eliminate any contrived tap dances to get around that, and future-proof a trigger against future such issues that could evolve, even if it’s not currently an issue.

I don’t have time (or need) to try this right now but if anyone has had the opportunity to check it out, please do comment on it here. Perhaps when I get past my current crunch time in a couple of months I can do some experiments. Thanks, Ron, for the great suggestion!

Brian Mullins January 17, 2014 at 2:54 pm

Jeff Humphreys, If you’ve specified a default value for your uniqueidentifier column (e.g. newid() or newsequentialid()) you can retrieve the default value by using the OUTPUT clause of the INSERT statement (http://technet.microsoft.com/en-us/library/ms174335(v=sql.105).aspx). The OUTPUT clause basically allow you to insert the rows from the [inserted] table (which contains your new GUIDs) into another table (e.g. temporary table or table variable). I have used this approach and it works.

Leave a Comment

Previous post:

Next post: