Simplifying “Instead Of” Triggers

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 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.

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 Boyle
Neil Boyle
Neil Boyle left school at the age of sixteen thinking that computers were things that only existed in Star Trek. After failed careers as a Diesel Mechanic, Industrial Cleaner, Barman and Bulldozer Driver he went back to college to complete his education. Since graduating from North Staffs Poly he has worked up through the ranks from Trainee COBOL Programmer to SQL Server Consultant, a role in which he has specialised for the past seven years.

Latest Articles