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.

Introduction

Much of what I'll talk about in this article can be found in many other

sources.  Steve

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:

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

program accordingly.

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

point:

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

sp_configure command:

To turn off nested triggers:

EXEC sp_configure 'nested triggers', 0

RECONFIGURE

To turn on nested triggers:

EXEC sp_configure 'nested triggers', 1

RECONFIGURE

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

following:

EXEC sp_dboption '<name of db>', 'recursive triggers'

To turn on recursive triggers:

EXEC sp_dboption '<name of db>', 'recursive triggers',

'true'

To turn off recursive triggers:

EXEC sp_dboption '<name of db>', 'recursive triggers',

'false'

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

ON Professors

FOR DELETE

AS

/* 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

TRANSACTION works.

Concluding Remarks

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!