The Alagad Technical Team Blog

Using SQL Server "INSTEAD OF" Triggers

Published By: Layne Vincent on May 5, 2009 at 11:34 AM
Categories:

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.

   1: USE [DB_Name]
   2: GO
   3: /****** Object:  Trigger [dbo].[trigger_name]    Script Date: 04/24/2009 14:10:37 ******/
   4: SET ANSI_NULLS ON
   5: GO
   6: SET QUOTED_IDENTIFIER ON
   7: GO
   8: CREATE TRIGGER [dbo].[trigger_name]
   9: ON  [dbo].[table_name]
  10: AFTER INSERT
  11: AS
  12: BEGIN
  13:     SET NOCOUNT ON;
  14:  
  15:     INSERT INTO
  16:         [other_table_name](
  17:         [col_1],
  18:         [col_2])
  19:     SELECT    
  20:         [col_1],
  21:         [col_2]
  22:        FROM    INSERTED
  23:     END

 

This looks fairly simple enough. The first 9 lines setup the creation of the trigger. If this were a situation where an existing trigger was to be altered, line 8 would read "ALTER TRIGGER".  Notice line 10, this particular trigger looks for an INSERT transaction on the table (line 9) to determine if it should fire or not. Assuming the table has been sent an INSERT statement the trigger then executes the code on lines 15-22. In this case a simple insert of a record set into another table. Notice also the FROM clause on line 22. This FROM clause refers to the INSERTED record set. This is the set of records inserted into the table on line 9. This will be important again later. Remember, this is an "AFTER INSERT" trigger. The trigger code will be executed after the INSERT is performed.

Let's move on to an "INSTEAD OF" trigger. The primary difference is that instead of performing the action defined in the trigger after the requested action has been performed on the database, the action in the trigger is performed instead of the action requested of the database. Effectively, an instead of trigger says, "I know what you were told to do but don't do it. Do this instead".
As we did in the example above, lets take a look at an INSTEAD OF trigger.

   1: USE [DB_name]
   2: GO
   3: /****** Object:  Trigger [dbo].[trigger_name]    Script Date: 04/24/2009 14:10:37 ******/
   4: SET ANSI_NULLS ON
   5: GO
   6: SET QUOTED_IDENTIFIER ON
   7: GO
   8: ALTER TRIGGER [dbo].[trigger_name]
   9: ON  [dbo].[table_name]
  10: INSTEAD OF UPDATE
  11: AS
  12: BEGIN
  13:     SET NOCOUNT ON;
  14:               
  15:     -- Determine if the record that was updated was a dulicate.
  16:     IF (NOT EXISTS    (SELECT d.[col_1]
  17:         FROM     deleted d, inserted i
  18:         WHERE   (d.[col_1] = i.[col_1] AND
  19:                 (d.[col_2] = i.[col_2] AND
  20:                 (d.[col_3] = i.[col_3] AND
  21:                 (d.[col_4] = i.[col_4])
  22:     )
  23:     BEGIN
  24:     -- If the record is not a duplicate,
  25:     -- update the table
  26:                     
  27:         UPDATE  [table_name]
  28:         SET     [table_name].[col_1] = i.[col_1],

		
2 responses to “Using SQL Server "INSTEAD OF" Triggers”
 
		
		
				

					
http://concealer.mybrute.com
Check out the cool mini-game

					
Posted By: Marcus on May 11, 2009 at 12:00 AM
					
				

			
				

					
This is exactly what I was looking for.  Thank you so much, you saved me a lot of time.

					
Posted By: reno812 on Jun 5, 2009 at 12:00 AM
					
				

			
	
	
		
Leave a Reply