"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
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
statement. Take this simple example:
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
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:
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.
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
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 = ',' )
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
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 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
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,