'INSTEAD OF' Triggers

SQL Server 2000 introduced a new type of trigger called an INSTEAD OF trigger. This new trigger extends SQL Server's trigger capabilities and provides an alternative to the AFTER trigger that was heavily utilized in prior versions of SQL Server.

The name of the trigger gives you some insight into how this new trigger operates. The INSTEAD OF prefix is relevant because this particular trigger performs its actions instead of the action that fired it. This is much different from the AFTER trigger that will perform its actions after the statement that caused it to fire has completed. This means that you can have an INSTEAD OF UPDATE trigger on a table that successfully completes but does not include the actual update to the table. Take a look at the following example.

The basic syntax for creating an INSTEAD OF trigger is this:

CREATE TRIGGER trigger_name 
ON table_name
SQL statements

In Listing 29.11, you will create a trigger that prints a message stating the number of rows updated by an UPDATE statement. You will then execute an UPDATE against the table that has the trigger on it. Finally, you will select the rows from the table for review.

Listing 29.11 A Simple INSTEAD OF Trigger
if exists (select * from sysobjects where id = object_id('dbo.cust_upd_orders')
        and sysstat & 0xf = 8)
        drop trigger dbo.cust_upd_orders
CREATE TRIGGER trI_au_upd ON authors
+CONVERT(VARCHAR(5), @@ROWCOUNT) + ' rows were updated.'

UPDATE authors
SET au_fname = 'Rachael'
WHERE state = 'UT'
TRIGGER OUTPUT: 2 rows were updated.

SELECT au_fname, au_lname FROM authors
WHERE state = 'UT'
au_fname             au_lname                          
-------------------- ----------------------------------------
Anne                 Ringer
Albert               Ringer

As you can see from the results of the SELECT statement, the first name (au_fname) column was not updated to 'Rachael'. The update statement is correct, but the INSTEAD OF trigger did not apply the update from the statement as part of its INSTEAD OF action. The only action that the trigger did is to print its message.

The important point to realize is that after you define an INSTEAD OF trigger on a table, you need to include all of the logic in the trigger to perform the actual modification as well as any other actions that the trigger might need to carry out.


To gain a complete understanding of the INSTEAD OF trigger, you must also understand its execution in relation to the other events that are happening. The following list details the key event execution relative to the INSTEAD OF trigger firing:

  • Triggering Action?The INSTEAD OF trigger fires instead of the triggering action. As shown earlier, the actions of the INSTEAD OF trigger replace the actions of the original data modification that fired the trigger.

  • Constraint Processing?This action happens after the INSTEAD OF trigger fires. This includes check constraints, unique constraints, and primary key constraints.

To demonstrate the trigger execution order, refer to the following trigger in Listing 29.12.

Listing 29.12 INSTEAD OF Trigger Execution
CREATE TRIGGER employee_insInstead
ON employee
DECLARE @job_id smallint

--Insert the jobs record for the employee if it does not already exist
   FROM jobs j, inserted i
  WHERE i.job_id = j.job_id)
   INSERT jobs
       (job_desc, min_lvl, max_lvl)
      SELECT 'Automatic Job Add', i.job_lvl, i.job_lvl
       FROM inserted i

--Capture the identify value for the job just inserted
--This will be used for the employee insert later
   SELECT @job_id = @@identity

   PRINT 'NEW job_id ADDED FOR NEW EMPLOYEE:' + convert(char(3),@job_id)


--Execute the original insert action with the newly added job_id
INSERT employee
       (emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)
   SELECT emp_id, fname, minit, lname, @job_id, job_lvl, pub_id, hire_date
     FROM Inserted


This trigger can be created in the Pubs database that ships with SQL Server 2000. The key feature of this INSTEAD OF trigger is that it can satisfy a referential integrity constraint that was not satisfied before the INSERT was executed. Note the foreign key constraint on the employee table that references the job_id on the jobs table. The trigger first checks to see whether the jobs record associated with the job_id of the employee being inserted exists. If the jobs record does not exist for the inserted employee's job_id, then the trigger inserts a new jobs record and uses it for the insertion of the employee record.

If you execute the following INSERT statement, which has a job_id that does not exist, it will succeed:

       (emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)
   VALUES ('KNN33333F', 'Kayla', 'N', 'Nicole', 20, 100, 9952, getdate())

That statement succeeds because the constraint processing happens after the INSTEAD OF trigger completes its actions. Conversely, if you were to create the same trigger as an AFTER trigger, the foreign key constraint would execute before the AFTER trigger and the following error message would be displayed:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 
'FK__employee__job_id__1BFD2C07'. The
conflict occurred in database 'pubs', table 'jobs', column 'job_id'.
-->The statement has been terminated.

Notice also, with the previous INSTEAD OF trigger example, that the last action that the trigger performs is the actual insertion of the employee record. The trigger was created to fire when an employee was inserted, so the trigger must perform the actual INSERT. This INSERT occurs in addition to any other actions that justify the trigger's creation.

AFTER Versus INSTEAD OF Triggers

Now that you have seen some of the key differences between the two types of triggers, you need to decide which trigger to use. In the previous example, the INSTEAD OF trigger was your only option for this kind of functionality. But, often, either trigger type can be used to attain the same result.

Something you should consider when making your choice is the efficiency of the overall modification. For example, if you have a modification that will cause a trigger to fire and often reject the modification, you might want to consider using the INSTEAD OF trigger. The rationale is that the INSTEAD OF trigger will not perform the actual modification until after the trigger completes. You will not need to undo the modification. If you were to use an AFTER trigger in the same scenario, any modifications that were rejected need to be rolled back because they have already been written to the transaction log by the time the AFTER trigger fires.

Conversely, if you have a situation in which the vast majority of the updates are not rejected, then the AFTER trigger might be your best choice. The particular situation will dictate the preferred type, but keep in mind that the INSTEAD OF triggers tend to be more involved. This is driven by the fact that you can only have one INSTEAD OF trigger on a table, and that trigger must perform the actual data modification that fired it.


One other consideration when coding INSTEAD OF triggers is that they can exist on the same table as an AFTER trigger. INSTEAD OF triggers can also execute based on the same data modifications.

Take, for example, the previous INSTEAD OF trigger that was placed on the employee table in the Pubs database that ships with SQL Server 2000. An AFTER trigger exists on the employee table by default (see Listing 29.13).

Listing 29.13 AFTER Trigger Placed on the Same Table as an INSTEAD OF Trigger
if exists (select * from sysobjects where id = object_id('dbo.employee_insupd')
        and sysstat & 0xf = 8)
        drop trigger dbo.employee_insupd

CREATE TRIGGER employee_insupd
ON employee
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
select @min_lvl = min_lvl,
   @max_lvl = max_lvl,
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
   raiserror ('Job id 1 expects the default level of 10.',16,1)
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
   raiserror ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)

This AFTER trigger checks whether the job level assigned to the employee falls within a valid range for the job_id that the employee was assigned. It is fired for both inserts and updates, and it can exist on the same table as the employee_insInstead INSTEAD OF trigger that was described earlier. The combined effect on an employee INSERT (with both the triggers on the employee table) is to have the following actions happen in the order listed:

  • The INSERT data modification is executed.

  • The INSTEAD OF trigger fires, completes its validation, and ultimately does the employee INSERT that is written to the transaction log.

  • Constraint Processing completes.

  • The AFTER trigger fires performing its actions on the employee record that was inserted by the INSTEAD OF trigger.

  • The AFTER trigger completes and commits the transaction to the database.

One of the key points in this example is that the AFTER trigger performs its actions on the row inserted by the INSTEAD OF trigger. It does not use the record from the original INSERT that started the trigger execution. Therefore, in this chapter's example, where the INSTEAD OF trigger generates a new job_id, the new job_id value is used in the AFTER trigger, not the job_id that was originally inserted.

Rollback and recovery need to be considered in this scenario as well, but it is beyond the scope of this discussion. This example simply shows you that these two types of triggers can be combined, and that the order of execution needs to be considered when designing your trigger solution.

Views with INSTEAD OF Triggers

One of the most powerful applications of an INSTEAD OF trigger is to a View. The INSTEAD OF trigger, unlike the AFTER trigger, can be applied to a View and triggered based on modifications to it. For more information on Views, see Chapter 27, "Creating and Managing Views in SQL Server."

The reason that this is so important is because data modifications have many restrictions when made via a View. The list is extensive, but following are a few examples:

  • You cannot use data modification statements that apply to more than one table in the View in a single statement.

  • All columns defined as NOT NULL in the underlying tables that are being updated must have the column values specified in the modification statement.

  • If the View was defined with the WITH CHECK OPTION clause, then rows cannot be modified in a way that will cause them to disappear from the View.

The INSTEAD OF trigger is a mechanism that can be used to overcome some of these restrictions. In particular, the first restriction (related to a single table modification) can be addressed with the INSTEAD OF trigger. The INSTEAD OF trigger fires before the actual modification takes place so it can resolve the modifications to the underlying tables associated with the View. It can then execute the modification directly against those base tables. This capability is demonstrated in the following example:

CREATE VIEW employeeJobs
select j.min_lvl, j.max_lvl, j.job_id, j.job_desc, e.job_lvl, e.emp_id
 from employee e, jobs j
where e.job_id = j.job_id

This creates a View in the Pubs database that joins data from the employee and jobs tables. It retrieves the job types and the associated levels, the employees assigned to the job types, and the employee's current job level. A sample set of rows from the view are shown next:

min_lvl max_lvl job_id job_desc                               job_lvl emp_id 
------- ------- ------ -------------------------------------- ------- ---------
25      100     14     Designer                               35      ENL44273F
25      100     14     Designer                               89      PSA89086M
25      100     14     Designer                               100     KFJ64308F
25      100     12     Editor                                 32      Y-L77953M
25      100     12     Editor                                 35      H-B39728F
25      100     12     Editor                                 100     HAS54740M

Perhaps you want to change the minimum job level (min_lvl) for the Designer job to 40 and at the same time set the job level (job_lvl) for any employees who have this job to 40 as well. If you execute the following update?without an INSTEAD OF trigger?against the View, you get the message shown:

UPDATE employeeJobs 
   SET min_lvl = 40,
       job_lvl = 40
 WHERE job_id = 12
View or function 'employeeJobs' is not updateable
because the modification affects multiple base tables.

To get around this problem, you can use an INSTEAD OF trigger. The trigger can decipher the update to the View and apply the updates to the base table without receiving the error. This functionality is demonstrated in the INSTEAD OF trigger found in Listing 29.14.

Listing 29.14 Basic View with an INSTEAD OF Trigger
CREATE TRIGGER employeeJobs_updInstead
ON employeeJobs
--update the data related to the jobs table
   SET jobs.min_lvl = i.min_lvl,
       jobs.max_lvl = i.max_lvl,
       jobs.job_desc = i.job_desc
  FROM inserted i
 WHERE jobs.job_id = i.job_id
   AND (jobs.min_lvl <> i.min_lvl
       OR jobs.max_lvl <> i.max_lvl
       OR jobs.job_desc <> i.job_desc)

--update the data related to the jobs table
UPDATE employee
   SET employee.job_lvl = i.min_lvl
  FROM inserted i
 WHERE employee.emp_id = i.emp_id

This example has a section that checks the fields related to the jobs table and updates the base table if any of the values have changed. It also has a section that updates the employee table for the employee fields that have been changed in the View.


This trigger could be enhanced to include logic to check for specific updates or to update only those employees who are assigned to the job and have a job level below the new minimum. These enhancements were not added to keep the example simple.

If you now execute the same update statement, you don't get an error message. You get the results shown here:

UPDATE employeeJobs 
   SET min_lvl = 40,
       job_lvl = 40
 WHERE job_id = 12

min_lvl max_lvl job_id job_desc                               job_lvl emp_id
------- ------- ------ -------------------------------------- ------- ---------
25      100     14     Designer                               35      ENL44273F
25      100     14     Designer                               89      PSA89086M
25      100     14     Designer                               100     KFJ64308F
25      100     13     Sales Representative                   35      PMA42628M
25      100     13     Sales Representative                   64      CGS88322F
25      100     13     Sales Representative                   100     TPO55093M
40      100     12     Editor                                 40      Y-L77953M
40      100     12     Editor                                 40      H-B39728F
40      100     12     Editor                                 40      HAS54740M

Notice that the Editor job now has a minimum level (min_lvl) equal to 40, and that all of the employees who have that job level (job_lvl) are also set to 40.

You can see the added flexibility that you get by using the INSTEAD OF trigger on a basic View. This flexibility is also applicable to a more sophisticated View called a Distributed Partitioned View. With this type of View, data for the View can be partitioned across different servers. This gives you the ability to scale your database solution and still have a single view of the data that appears as one table.

You can make data modifications via a Distributed Partitioned View, but some restrictions exist. In the case in which the requirements are not met for updating the View, the INSTEAD OF trigger can be used to bypass these restrictions; this is similar to the previous example.

For a more in-depth discussion of Distributed Partitioned Views, see Chapter 21, "Administering Very Large SQL Server Databases."

INSTEAD OF Trigger Restrictions

You have seen many of the capabilities of INSTEAD OF triggers, but they also have limitations. The following list shows you some of them:

  • INSTEAD OF triggers do not support recursion. This means that they cannot call themselves regardless of the setting of the Recursive Triggers database option. For example, if an INSERT is executed on a table that has an INSTEAD OF trigger and the INSTEAD OF trigger performs an INSERT on this same table, then the INSTEAD OF trigger for this INSERT will not fire for a second time. Any AFTER triggers defined on the same table for INSERT will fire based on the INSTEAD OF trigger INSERT.

  • You can define only one INSTEAD OF trigger for each action on a given table. Therefore, you can have a maximum of three INSTEAD OF triggers for each table: one for INSERT, one for UPDATE, and one for DELETE.

  • A table cannot have an INSTEAD OF trigger and a foreign key constraint with CASCADE defined for the same action. For example, you cannot have an INSTEAD OF trigger defined for delete on a given table as well as a foreign key with a CASCADE DELETE definition. You will receive an error if you attempt to do this. In this situation, you could have INSTEAD OF triggers defined on INSERT and UPDATE without receiving an error.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features