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

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

By Brian Kelley, (first published: 2001/09/03)

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:

CREATE TRIGGER <trigger name>
ON <table name>
FOR <operation>
AS
<DML statements>
CREATE TRIGGER <trigger name>
ON <table name>
AFTER <operation>
AS
<DML statements>

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.

Total article views: 72580 | Views in the last 30 days: 17
 
Related Articles
ARTICLE

Simplifying Instead Of Triggers

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

FORUM

Trigger and @@Identity

How not to get the @@Identity of the Trigger Insert

BLOG

Instead Of Trigger and OUTPUT clause headache

It is known that you cannot get inserted identity values using OUTPUT clause when inserting to view ...

FORUM

Instead Of Triggers

Triggers

FORUM

instead of trigger problem

instead of trigger not allowing update at all

Tags
sql server 7    
triggers    
 
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