Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2000 Trigger Enhancements

By Neil Boyle,

SQL Server 2000 Trigger Enhancements


Introduction

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.

If you are already familiar with programming triggers then read on, but if you are new to the subject then it would be worth reading about triggers in Books Online, or taking a look at the Triggers section on my own SQL Server page, before reading this article.

After Triggers

After Triggers are the type of trigger that existed prior to SQL Server 2000, the "After" name is new and helps differentiate between this type of trigger and the new "Instead Of" trigger type, which will be covered later.

Multiple After Triggers

More than one trigger can now be defined on a table for each Insert/Update/Delete. Although in general you might not want to do this (it’s easy to get confused if you over-use triggers) there are situations where this is ideal. One example that springs to mind is that you can split your triggers up into two categories:

  • Application based triggers (cascading deletes or validation for example)
  • Auditing triggers (for recording details of changes to critical data)

This would allow you to alter triggers of one type without fear of accidentally breaking the other.

If you are using multiple triggers, it is of course essential to know which order they fire in. A new stored procedure called sp_settriggerorder allows you to set a trigger to be either the "first" or "last" to fire.

If you want more than two triggers to fire in a specific order, there is no way to specifically define this. A deeply unscientific test I did indicated that multiple triggers for the same table and operation will run in the order they were created unless you specifically tell them otherwise. I would not recommend relying on this though.

Instead Of Triggers

Instead Of Triggers fire instead of the operation that fires the trigger, so if you define an Instead Of trigger on a table for the Delete operation, they try to delete rows, they will not actually get deleted (unless you issue another delete instruction from within the trigger) as in this simple example:

create table test1 (
    vc     varchar(32)
)
go

create trigger tr_test1_o on test1 instead of delete
as
    print 'Sorry - you cannot delete this data'
go

insert test1
    select 'Cannot' union
    select 'delete' union
    select 'me'
go

delete test1
go

select * from test1
go

drop table test1

If you were to print out the contents of the Inserted and Deleted tables from inside an Instead Of trigger you would see they behave in exactly the same way as normal. In this case the Deleted table holds the rows you were trying to delete, even though they will not get deleted.

Instead of Triggers can be used in some very powerful ways!

  • You can define an Instead Of trigger on a view (something that will not work with After triggers) and this is the basis of the Distributed Partitioned Views that are used so split data across a cluster of SQL Servers.
  • You can use Instead Of triggers to simplify the process of updating multiple tables for application developers.

Mixing Trigger Types

If you were to define an Instead Of trigger and an After trigger on the same table for the same operation, what would happen?

Because an After trigger fires after an operation completes, and an ‘instead of’ trigger prevents the operation from taking place, the After trigger would never fire in this situation.

However, if an Instead Of trigger on a (say) delete operation contains a subsequent delete on the same table, then any After trigger defined for the delete operation on that table will fire on the basis of the delete statement issued from the Instead Of trigger. The original delete statement is not executed, only the Delete in the Instead Of trigger runs.

This code sample creates a trigger of each type, and changed the nature of the delete statement issued so that only comics that have a value of 0 in the preserve column can be deleted.

create table test2 (
    comic        varchar(32),
    preserve     int
)
go

insert test2
    select 'Groucho', 1 union
    select 'Chico', 1 union
    select 'Harpo', 0 union
    select 'Zeppo', 0
go

create trigger tr_test2_delete on test2 for delete
as
    select comic as "Deleting_these_names_only"
    from deleted
go

create trigger tr_test2_instead_of on test2 instead of delete
as
    delete test2
    from test2
    inner join deleted
    on test2.comic = deleted.comic
    where test2.preserve = 0
go

delete test2 where comic in ('Groucho', 'Harpo')
go

select * from test2

drop table test2

Further Reading

For more details on Instead Of triggers take a look at this article, and of course Books Online.

About the author

Neil Boyle is an independant 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

Total article views: 6671 | Views in the last 30 days: 0
 
Related Articles
FORUM

Recursive Deletion using Triggers

Instead of Delete Trigger

FORUM

Instead Of Triggers

Triggers

FORUM

instead of trigger problem

instead of trigger not allowing update at all

ARTICLE

Simplifying Instead Of Triggers

Instead of triggers are a new feature in SQL Server 2000 which greatly extend the functionality of t...

FORUM

TRIGGERS AFTER UPDATE,DELETE

Is it possible to write a single trigger for UPDATE and DELETE and allow some Delete specific code ?...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones