Triggers in SQL Server 7.0 and 2000 - The Common Ground
This is a two part series on how triggers work in the two latest versions
of MS SQL Server. Because there are some pretty significant additions in
trigger functionality from 7.0 to 2000, we'll first need to look at what the two
versions have in common. The second part to this series will look at the
differences between the two MS SQL Server versions.
Much of what I'll talk about in this article can be found in many other
Jones writes in his article Career
Advice for DBAs that "Most SQL Server books contain about 80% of
the same information." I have to agree with him. My goal,
though, is to condense some of the disparate sources of information out there
together into this one article. So without further ado, let's look at what
we'll talk about:
- What triggers are and when they fire
- When would we use them
- When would we avoid them
- Nested and recursive triggers
- The inserted and deleted tables
- Triggers and transactions
What Triggers Are and When They Fire
Triggers are simply special stored procedures that fire after a specified
T-SQL statement (and in SQL 2000, the INSTEADOF triggers can fire before).
They respond to INSERT, UPDATE, and/or DELETE operations at the table
level. They are created in much the same manner as a standard stored
procedure with two additions. Here's a sample trigger creation statement
on the Employees table:
SELECT EmployeeID, FirstName, LastName, 'Deleted' Operation
The ON keyword tells what table the trigger relates to and the FOR keyword
specifies the operation to fire in regards to. Multiple operations can be
specified. In addition, multiple triggers can be defined for a particular
operation on a table. For instance, we could have five triggers that fire
based on an INSERT operation for say the Authors table. The key to
remember with multiple triggers is that they fire in no specified order.
As a result, it is simply best to assume they all fire instantaneously and
When Would We Use Them
There are times when Declarative Referential Integrity (DRI) simply won't do the
job for us. Foreign keys can ensure that we don't insert a value in a
child table without a matching value in the parent table, and they can insure we
don't delete or change a value in the parent table that would cause records in
the child table(s) to be orphaned. What they can't do, however, is enforce
referential integrity across databases or ensure a 1 to n relationship where n
is a specific number. Let me note that DRI isn't just foreign key
constraints. But in this article, when I refer to DRI, I'm only referring
to foreign key constraints.
Now, if we have a requirement to log all data changing operations on
a particular table (expensive, but sometimes necessary), triggers may be our
best option. Generally, developers don't like writing a ton of extra code
to add this auditing into their stored procedures and it falls to the DBAs to
ensure the auditing is done.
Finally, we might use them to modify certain "computed"
columns. For instance, consider a simple sales database. The total
sales for a given day could be obtained by creating an aggregate query on all
sales for the day in question, such as at the end of a particular shift.
If the store has a decent business, this is an expensive query, to say the
least. Triggers can work wonders by updating a table that keeps running
totals of sales and other pertinent information. In that case, instead of
making the aggregate queries over numerous records, triggers can add the total
for a particular sale and modify a table holding the running totals.
Queries against that running totals table are very light.
When Would We Avoid Them
Triggers add extra overhead to any of the specified operations that we
do. That's the bottom line. Sometimes triggers are necessary or the
best solution. Other cases, we'd be better off choosing another approach.
If we've got a choice of DRI, that's not a hard choice. If you need a
trigger to fire on only a small subset of possible inserts, updates, or deletes,
then it is probably better to look at the stored procedures. Case in
I'm active on the Lawson System Administrators Topica list and a person asked
about a trigger he was writing. Whenever a particular table has an INSERT,
he needs to check the ACTION_CODE to see if it is equal to 'HIRE' or not.
He only needs the trigger to fire when ACTION_CODE = 'HIRE' and that represents
a small subset. Normally here we'd say, "How about in the
stored procedures that do your setup for a new hire... put the auditing
code there." And that makes a lot of sense, because percentage wise,
ACTION_CODE = 'HIRE' isn't very great. The trigger is firing more times
than not when ACTION_CODE isn't for a new hire. Which brings me back to
when we would use them. Lawson controls the database objects with respect
to stored procedures, tables, indexes, etc. Tinker with what Lawson has
defined and prepare to stand by. One could say the user in question is
risking a lot by defining a trigger! However, this is a case where quite
simply, there is no other way to do what he wants. It's not a very
satisfactory approach, but it happens to be the only approach. If there's
another approach, then it's worth looking into.
Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other
triggers. For instance, I delete a row from TableA. A trigger on
TableA fires to delete rows from TableB. Because I'm deleting rows from
TableB, a trigger fires on TableB to record the deletes. This is an
example of a nested trigger. As we've talked about, SQL Server 7.0 doesn't
support cascading updates and deletes based on foreign key relationships.
Therefore, if we want to relate our data we can't use DRI and must resort to
triggers or some application oversight. Let's say we've got a cascade
delete to fire down 3 or 4 tables. Nested triggers are our answer.
Our delete on a particular table fires a trigger which deletes rows for another
table, which fires a trigger, so on and so forth. SQL Server 7 and 2000
support up to 32 levels of nested triggers.
Now the big question is, does my SQL Server allow nested triggers?
That's an easy question to answer. It's on by default, but in Query
Analyzer we can issue the following command:
EXEC sp_configure 'nested triggers'
If your run_value is set to 0, your server isn't allowing nested
triggers. If it's set to 1, nested triggers may fire. This is a
server wide setting. Now, to change your setting, once again use the
To turn off nested triggers:
EXEC sp_configure 'nested triggers', 0
To turn on nested triggers:
EXEC sp_configure 'nested triggers', 1
Now, recursive triggers are a special case of nested triggers. Unlike
nested triggers in general, support for recursive triggers is at the database
level. So what exactly are recursive triggers? As the name implies,
there is recursion going on, which means a trigger is eventually going to call
itself. There are two types: direct and indirect.
Direct: A direct trigger is a trigger that performs the same
operation (INSERT, UPDATE, or DELETE) on the same table causing the trigger to
fire itself again.
Indirect: An indirect trigger is a trigger that fires a trigger on
another table and eventually the nested trigger ends up firing the first trigger
again. For instance, an UPDATE on TableA fires a trigger which causes an
UPDATE on TableB. The UPDATE on TableB fires a trigger which does an
UPDATE on TableC. TableC has a trigger which causes an UPDATE on TableA
again. TableA's UPDATE trigger fires again.
Recursive triggers can get pretty convoluted just as those initial recursion
exercises in a programming class seemed out in left field. As a result,
recursive triggers are turned off by default. However, if you want to
check to see the status of recursive triggers in a particular database, do the
EXEC sp_dboption '<name of db>', 'recursive triggers'
To turn on recursive triggers:
EXEC sp_dboption '<name of db>', 'recursive triggers',
To turn off recursive triggers:
EXEC sp_dboption '<name of db>', 'recursive triggers',
By the way, you can view and change these settings in Enterprise Manager as
well by looking at the properties for the server or database respectively.
The inserted and deleted Tables
When dealing with triggers, we have two special tables to use and they are inserted
and deleted. For an INSERT or DELETE operation, inserted and
deleted are pretty self-explanatory. They contain the rows inserted
or removed. The only tricky part is with UPDATE operations. In that
case the deleted table contains the rows touched as they were before they
were altered (the old rows). The inserted table contains the rows as they
are after the change (the new rows). Think of it in these terms: delete the old rows, insert
the new rows. And that explains how the two tables work. It
should be noted that we can test for changes on specific columns, but that's
beyond the scope of our discussion.
Now there is a catch with reference to text, ntext, and image
columns. The inserted and deleted tables won't have any
columns with those data types (SQL 2000 allows them in the INSTEADOF triggers,
but that'll be looked at in the next article). So if we need to handle
those types of columns in our operations, we'll need to put the code to do so in
stored procedures and the like, because triggers won't work for us.
Triggers and Transactions
If we're updating multiple tables, and either all the updates must take or
none should, we'll frequently go to transactions in order to ensure this all or
nothing requirement. A ROLLBACK TRANSACTION at any step of the process
rolls everything back like it never happened. The same is true if a
ROLLBACK TRANSACTION is called within a trigger.
Let's say we have an UPDATE trigger on TableC that is there to check to see
that an UPDATE operation follows certain business rules. Now for
this example, it is the third table to be updated (hence TableC). The UPDATE
occurs and the trigger fires. Upon checking the UPDATE, the new data isn't
valid based on our pre-defined business rules. So the trigger on TableC calls
ROLLBACK TRANSACTION. Not only are the updates to TableC rolled back, but
the entire transaction, which means the updates to TableA and TableB are as
well. We don't have to write any specialized logic in the trigger on
TableC to figure out which rows in TableA and TableB changed and seek to undo
those changes. ROLLBACK TRANSACTION within the trigger takes care of
it for us.
This is all well and good when we've executed that BEGIN TRANSACTION, but
what happens if we haven't? What happens if we've just done a DELETE
against a single table? How do we undo our changes from the trigger?
Thankfully, though we've not formally declared a transaction, SQL Server
institutes one. The BEGIN TRANSACTION is implied. So if we call
ROLLBACK TRANSACTION from our trigger, our DELETE is rolled back without any
further effort on our part. Here's an example:
CREATE TRIGGER trig_delete_Professors
/* Check to see if a professor has tenure */
DECLARE @tenure bit
SET @tenure = (SELECT Tenure FROM deleted)
/* If the professor has tenure, we can't fire him. */
IF (@tenure = 1)
ROLLBACK TRAN /* TRAN[SACTION] */
The key to remember is that even if we haven't explicitly declared a
transaction, triggers operate with an implicit transaction and ROLLBACK
This article was intended as an overview of triggers in SQL Server 7.0 and
2000. Hopefully I've accomplished that goal. We've covered that
triggers are special stored procedures and discussed a little bit about when
they fire. We've talked about some general instances of when to use them
and when to go looking for something else. We also looked at what nested
and recursive triggers are, and how we can turn them on and off. We
explored the inserted and deleted tables and discussed how
transactions and triggers interface. In the next article we'll be looking
at some of the differences between SQL Server 7.0 and 2000. The really big
differences have to do with how DRI has been upgraded and the inclusion of
INSTEADOF triggers that fire before an operation. For the most part,
everything else has remained the same. Until next time!