Using SQL Server "INSTEAD OF" Triggers
Recently on a project we found ourselves in a situation where we needed a way eliminate updates to tables when those updates were acting upon records that had not been altered. I know, your first thought is "just don’t do the update if the records haven’t changed". That was our first thought as well. Unfortunately, that would have required significant refactoring of the application code and, in this case, that solution was just not appropriate. So a different approach was needed and we found it in the use of "INSTEAD OF" triggers. Before I get into the use of "INSTEAD OF" triggers, let me do a little "Trigger 101" for you. In simplest possible terms, triggers wait for an action to be requested of the database and then act upon that action in a specified manner. Once an action is requested, SQL Server looks to see if a trigger exists and if so, it performs whatever code has been written into that trigger. Pretty straight-forward, huh? So lets take a look at a simple trigger.