Simplifying "Instead Of" Triggers

May 1, 2000


"Instead of" triggers are a new feature in SQL Server 2000 which greatly extend the functionality of triggers. This article covers a basic introduction to Instead of Triggers and illustrates possibilities for their use.

The article was developed under beta release 2, and thus is subject to change when the final version of SQL Server is released. The article assumes some knowledge of Update, Insert and Delete triggers as used in previous versions of SQL Server.

What are "Instead Of" triggers?

In previous versions of SQL Server, triggers were sections of code that were attached to tables and executed automatically after pre-defined updates took place on a specified. table. These of course still exist in SQL Server 2000. Instead of triggers are attached to a table in a similar way, but the code inside them is executed in place of the original updating statement. Take this simple example:

(Click here for code example 1.)

Note that the trigger still has access to the Inserted and Deleted tables, which work in the same way as old-style triggers. What is different is the contents the test1 table as displayed from within the trigger: For old-style triggers the contents of the test1 table would more closely match the contents of the Inserted table because the trigger fires after the update, but here the data selected from test1 is unchanged because no update is actually taking place (remember--the trigger happens instead of the update)

Attaching triggers to a view

An instead of trigger can be defined on a view. Using this, we can now update views when more than one underlying table is updated. In this simple example we will create a view on two related tables and insert both tables via one insert command on the view:

(Click here for code example 2.)

Once the insert has run, check the contents of the city and country tables they should now contain distinct entries for citied and countries. The code in the trigger prevents duplicate entries for city and country names and automatically sorts out the relationship between the two tables.


Views are commonly cited as a method of increasing security by allowing users only to access specific subsets of data, and denying access to underlying tables. "Instead of" views extends this by allowing trusted users a way of updating multiple tables through the view they normally select from. In previous versions such a function would have to be implemented in a stored procedure, which is not a problem in itself, but it means that your user had more things to remember.

Attached clients

As an added bonus, multiple tables can now directly updated by clients such as Microsoft Access that attach only to the view. As described above, the user needs permissions on the view only (allowing for the normal "Ownership chain" rules applied in SQL Server) No extra SQL code either at the client or server end is required.

Using "Instead Of" triggers for bulk insertion

"Instead of" triggers are compatible with the Bulk Insert feature, so we can choose to load data to the server through updateable views. Handy for importing data from legacy systems, text data attached to Emails, and all those other "awkward" sources. Note the use of the FIRE_TRIGGERS option in the example below - without this the bulk insert operation would fail.

bulk insert v_geography
from 'g:\cities.txt'
with (
    datafiletype = 'char',
    fieldterminator = ','

Multiple triggers

You cannot create more than one "Instead Of" trigger on a table or view for each operation (Insert, Update or Delete) but you can have "(instead Of" triggers mixing with the the old style of triggers. If you do, then the following rules apply.

  • The "Instead Of" triggers fire first

  • If there is no update to the original target table defined in the "Instead of" trigger, subsequent triggers on that table will not fire

  • If the "Instead Of" trigger does update the original target table, then other triggers defined on that table will fire as appropriate

  • If the "Instead Of" trigger converts the update operation type (for example converting an Update into Delete and Insert operations) then the triggers that relate to the operation defined inside the "Instead of" trigger will fire. If you cut-and-paste this simple example into Query Analyser and run it, you will see that the Update trigger never actually gets fired by the update command, but the insert/delete trigger gets fired twice--once each for the Insert and Delete operation defined in the "Instead Of" trigger.

    drop table multiTest
    create table multiTest (
        keyVal int
    insert multiTest select 1 union select 2 union select 3
    create trigger tr_multiTest_io on multitest instead of update as
        select 'instead of trigger firing'
        delete multiTest
        from   multiTest inner join deleted on multiTest.keyVal = deleted.keyVAl
        insert  multiTest
        select * 
        from   inserted
    END -- trigger def
    create trigger tr_multiTest_u on multitest after update as
        select 'update trigger firing'
    create trigger tr_multiTest_id on multitest after insert, delete as
        select 'insert/delete trigger firing'
    update multitest set keyVal = keyVal + 1

Partitioned data

Partitioned data is simply data that is split up and stored in multiple tables. For example, if your customer table is getting to big to maintain you can split it into separate, smaller tables and locate them on different disk subsystems (by splitting the database across multiple files on multiple disk arrays) or even on different servers using SQL Server 2000 Federated Database facilities.

Splitting large tables up is nothing new in itself, many organizations maintain large data by splitting into "current" and "archive" sections--data capture running against the smaller "current" table, and reporting, etc ran against the "archive" data or both. Batch jobs will move data from "current" to "archive" as and when business rules require.

The main problem with this technique is that it is very inflexible, and client software is required to know where to look for the data it needs. Now that you can use "Instead Of" triggers and Partitioned views, this restriction is lifted, and the user or client process needs to only know about a single view in order to see or maintain data spread across multiple tables, or even multiple servers.

About the author


The Network for Technology Professionals


About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers