Triggers in SQL Server 7.0 and 2000 - What's New

This is the second of a two part series on how triggers work in the two

latest versions of MS SQL Server.  The first part dealt with what is common

between the two versions.  This article will detail what's been added to

SQL Server 2000.

Introduction

In the first part of this series we looked at how triggers were similar

between the two latest versions of SQL Server.  With the introduction of

SQL Server 2000, Microsoft brought forth cascading referential integrity and the

INSTEAD OF trigger, a trigger that fires before the INSERT, UPDATE, or DELETE

statement is conducted.  These two changes lead to some additional concepts

that need to be considered before implementing triggers in a database. 

We'll look at the following:

• Additions to @@IDENTITY

• Setting trigger firing order

• INSTEAD OF triggers

• Consequences of Cascading Referential Integrity

Before we get started, let me point out a couple of things that were missed

in the first article.  The first thing is that a trigger fires based on an

INSERT, UPDATE, or DELETE statement.  It doesn't fire per row, but per

statement.  As a result, we have to remember any operations we might

execute in the trigger are set-based.  So all tests, etc., we perform or

business rules we choose to implement must be built with this in mind. 

Second, under SQL Server 7 and 2000, @@IDENTITY returns the identity value for

the last row that was inserted.  This means that if we insert into a

particular table, and that fires a trigger that does an insert into another

table, both tables with IDENTITY properties defined, the @@IDENTITY that is

going to be returned is the one from the trigger's insert, not the original

statement.  In SQL Server 2000, Microsoft has given up a couple of

functions that handle this dilemma, which is where we'll start.

Additions to @@IDENTITY

As I stated in the introduction, @@IDENTITY proved to be a problem in SQL

Server 7.0 if we used a trigger that did an insert into a table that also had an

IDENTITY property defined on a column.  SQL Server 2000 introduces two new

functions to help us deal with this issue:  IDENT_CURRENT() and

SCOPE_IDENTITY().  

IDENT_CURRENT()

IDENT_CURRENT('<table name>') returns the last identity value

generated for the table specified.  This works great in the case where we

are using a trigger that inserts into another table that has an IDENTITY

column.  For instance, consider the following:  table Employee has an

IDENTITY column, EmployeeID.  When we hire someone, we want the information

recorded in another table, which we'll call EmployeeHistory that also happens to

have an IDENTITY column.  Here is how a particular trigger might be

declared:

CREATE

TRIGGER trig_insert_Employee
ON Employee
FOR INSERT
AS
INSERT

EmployeeHistory (FirstName, LastName, Employee Action, ActionDate)
SELECT

FirstName, LastName, 'Hire', GETDATE()
FROM inserted

If we were to perform a SELECT @@IDENTITY after the INSERT statement

for Employee in order to get the EmployeeID for our new hire, the @@IDENTITY

returns the identity value from the INSERT on the EmployeeHistory table. 

This isn't what we want!  It isn't an issue in SQL Server 2000 because we

can simply execute SELECT IDENT_CURRENT('Employee') and that'll return

the last identity value on the Employee table, which is what we were

after. 

SCOPE_IDENTITY()

SCOPE_IDENTITY() was built for the same reason as IDENT_CURRENT(), to be able

to retrieve the correct identity value.  SCOPE_IDENTITY(), as the name

implies, works based on the scope where it's called.  It'll return the

identity value based on an INSERT in the same scope.  So for our example

above, the trigger is considered to be a different scope, and as a result,

SELECT SCOPE_IDENTITY() won't pick up identity value from the INSERT

based on the trigger, but instead the INSERT we performed on the Employee table.

Scope is based on stored procedure, trigger, or batch.   An INSERT in

a different scope will change @@IDENTITY, but it won't change

SCOPE_IDENTITY().

Setting Trigger Firing Order

In SQL Server 7.0, triggers were understood to be fired in no particular

order.  If order was necessary, then the various triggers needed to be

rolled up into a single trigger.  In SQL Server 2000, a new stored

procedure has been added called sp_settriggerorder.  The purpose of

sp_settriggerorder is to set for a particular operation (INSERT, UPDATE, or

DELETE) for a particular table which trigger will fire first and/or which

trigger will fire last.  Any and all triggers not specified as first or

last will fire in no particular order.  Our stored procedure has the

following syntax:

EXEC sp_settriggerorder <trigger name>,

<order>, '<operation>'

For instance:

EXEC

sp_settriggerorder trig_insert_Employee, first, 'INSERT'

We have three choices on order: first, last, and none.  We can use none

to toggle a trigger to no longer fire first or last.  For instance:

EXEC

sp_settriggerorder trig_insert_Employee, none, 'INSERT'

One of the reasons we might want to do this is if we want to make another

trigger first or last.  If there is already a trigger defined as first or

last, it must be set to none before another trigger could be set to first or

last.  Let's suppose that we've built another trigger for INSERT on

Employee, which we'll call trig_insert_Employee_Audit.  Internal Audit

wants us to record the exact inserts to the Employee table, and this needs to be

carried out before any and all operations, to include our simple INSERT to

EmployeeHistory.  If we were to enter the following and

trig_insert_Employee was still set as first, we'd receive an error:

EXEC

sp_settriggerorder trig_insert_Employee_Audit, first, 'INSERT'

The error SQL Server would return is:

Server: Msg 15130, Level 16, State 1, Procedure

sp_settriggerorder, Line 66
Table '[dbo].[Employee]' already has a

'first' trigger for 'INSERT'.

As can be seen, sp_setstriggerorder won't simply replace trig_insert_Employee

with trig_insert_Employee_Audit as the first trigger to fire on an INSERT

operation. This is why we need to first use sp_settriggerorder to set

trig_insert_Employee to none for firing order.

INSTEAD OF Triggers

SQL Server 7 only has what SQL Server 2000 calls AFTER triggers. The

language for declaring triggers using FOR still works, but now it is

interchangeable with AFTER.  So the following are valid declarations for

triggers that fire after the operation completes:

SQL Server 2000 adds the INSTEAD OF trigger, which fires before the operation

is executed on the table.  There are a couple of limitations on INSTEAD OF

triggers.  If a table is the target of a cascaded DELETE or UPDATE

operation, an INSTEAD OF trigger cannot be defined on the table for that

particular operation.  More on this later.  Also, unlike AFTER

triggers, only a single INSTEAD OF trigger can be defined for a particular

operation on a table.  

INSTEAD OF triggers are declared in much the same manner as the AFTER

trigger, as follows:

CREATE

TRIGGER <trigger name>
ON <table name>
INSTEAD

OF <operation>
AS
<DML statements>

One nice thing is that the inserted and deleted tables are still available to

us, and these contain the information that we would expect from a normal AFTER

trigger.  With respect to UPDATE, deleted still contains the rows

affected as they exist before the expected UPDATE, and inserted contains

the rows as they would be after the UPDATE.  Another difference between

AFTER triggers and INSTEAD OF triggers is that columns of type text, ntext, and

image are found in the inserted and deleted tables for the INSTEAD

OF triggers.  This means if we have business rules that might need to check

columns of these types, we can employ enforcement of these business rules with

the INSTEAD OF trigger. Also, INSTEAD OF triggers may be defined on views, which

is not true of AFTER triggers.

Consequences of Cascading Referential Integrity

Cascading referential integrity is a nice addition to SQL Server 2000,

especially when we consider that MS Access has had it for a while!  In any

case, it does allow us to do cascaded UPDATE and DELETE statements without a lot

of fuss.  The only additional specification that we have to do is specify

ON UPDATE CASCADE and/or ON DELETE CASCADE when we declare our foreign key

constraint.  The default is NO ACTION which functions in a manner that

we're used to, where an error is returned and the transaction is rolled

back.  When we start mixing cascading referential integrity and triggers,

we've got a few things to keep in mind on how everything operates. Let's take a

look:

INSTEAD OF Triggers

As previously mentioned, if a table is a target of cascading referential

integrity (in other words, the table with the foreign key constraint), it cannot

have an INSTEAD OF trigger defined for the operation(s) which cascade.  For

instance, if we have ON UPDATE CASCADE defined, but we don't have ON DELETE

CASCADE defined, we can build an INSTEAD OF trigger for DELETE, but not for

UPDATE.  Similarly, if we already have an INSTEAD OF trigger for an

operation, we can't alter the table to add/alter a constraint for that operation

which allows the cascade to occur. In both cases we'll get an error.  This

makes sense, because an INSTEAD OF trigger can undo the very changes that need

to be cascaded.  We can have one or the other, but not both, for a given

operation.

Order of Operations

Mixing cascading referential integrity and triggers means we have to be aware

of the order in which everything takes place.  It can be a little tricky,

but here are some ground rules:

• An INSTEAD OF trigger for the table on which the UPDATE or DELETE

  operation is performed will fire first.  As always, the INSTEAD OF

  trigger can stop the operation dead in its tracks.

• The referential operation will take place next.

• AFTER triggers based on the original referential operation will fire next

  if any rows have been changed in a particular table.

• Cascaded operations caused by AFTER triggers will fire and are treated

  independently of other operations.

Books Online has some additional detail on the subject under the Index

tab with the subject of "cascading referential integrity constraints" which

outline the firing order for AFTER triggers.  However, my tests don't

confirm what Books Online is saying.  It states that the referential

operations fire first.  So far so good.  But it then states that the

AFTER trigger on the original table is fired next, followed by the cascaded

tables.  In my own tests, I've found that it works from the bottom up,

where the original table's AFTER triggers fire last.  This is one case

where you'll probably want to experiment for yourself.

Concluding Remarks

SQL Server 2000 adds several features with regards to triggers that are

significant improvements over what we had available in SQL Server 7.  First

and foremost, Microsoft did attack the @@IDENTITY issue by using IDENT_CURRENT()

and SCOPE_IDENTITY().  These two functions allow us to get the identity

value we were intending all along, without having to worry about what a trigger

might do.  Second, we can specify a trigger to fire first or last on a

given operation by using sp_settriggerorder, which is far more control than we

had in SQL Server 7.  Finally, SQL Server 2000 introduces INSTEAD OF

triggers, which allow us to do our checks and the like prior to the execution of

an operation.  However, with these enhancements, we do still have to be

careful, especially since Microsoft has also added cascading referential

integrity, and with added functionality comes added complexity as we consider

how to mix and match our various options for the best possible solution.

This set of two articles is by no means comprehensive.  It is a

highlight on what's common between SQL Server 7 and 2000 and what has been added

in the latest version.  For more information, two good sources are Books

Online and Microsoft Press' Inside SQL Server 2000, written by Karen

Delaney.  Triggers represent just another tool in our toolbox towards

deploying the best possible database solution.