Introduction

"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 pervious 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:

create table test1 (descr varchar(32))
go
insert test1 
 select 'Quick demo of' union
 select 'instead of triggers' 
go
create trigger tr_test1_io on test1 instead of update as
BEGIN
 select descr as 'Inserted Columns' from inserted
 select descr as 'Deleted Columns' from deleted
 select descr as 'Table test1' from test1
END -- trigger def
go
update test1 set descr = NULL
go
drop table test1

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:

create table country (
 countryID int identity,
 countryName varchar(32),
)
go
create table city (
 cityID int identity ,
 CityName varchar(32),
 countryID int,
)
go

create view v_geography as
 select countryName, cityName 
 from country inner join city on country.countryID = city.countryID
go

create trigger tr_v_geography_io on v_geography instead of insert as
BEGIN
 insert country (countryName)
 select distinct inserted.countryName 
 from inserted left join country on inserted.countryName = country.countryName
 where country.countryName IS NULL /*** Exclude countries already in the table ***/

insert city (cityName, countryID)
 select distinct inserted.cityName, country.countryID
 from inserted inner join country on inserted.countryName = country.countryName
 left join city on inserted.cityName = city.cityName 
 where city.cityName IS NULL /*** Exclude cities already in the table ***/

END -- trigger def
go

insert v_geography 
select 'England', 'London' UNION
select 'England', 'Manchester' UNION
select 'Japan', 'Tokyo' UNION
select 'Japan', 'Osaka' UNION
select 'USA', 'Washington DC' UNION
select 'USA', 'New York' 

Once the insert has run, check the contents of the cityand 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.

Security

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 (
 FIRE_TRIGGERS,
 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
go
create table multiTest (
 keyVal int
)
go
insert multiTest select 1 union select 2 union select 3
go
create trigger tr_multiTest_io on multitest instead of update as
BEGIN
 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
go
create trigger tr_multiTest_u on multitest after update as
 select 'update trigger firing'
go
create trigger tr_multiTest_id on multitest after insert, delete as
 select 'insert/delete trigger firing'
go
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

Neil Boyle is an independent SQL Server consultant working out of London, England. Neil's free SQL Server guide is available on-line at http://www.impetus-sql.co.uk