Most SQL users are at least aware of the existence of triggers and that they

fire as a result of a insert, update, or delete statement. There are quite a few

options and ideas around the use of triggers and my goal today is to introduce a

few of those to you. I'll try to avoid pulling the trigger on any bad puns!

Tip #1 - Triggers can be disabled instead of dropped

There may be times when you will want to avoid the overhead of a trigger;

perhaps you're making a large number of updates to a table. You can script out

the trigger and drop it, or just disable it using the syntax 'alter table

TABLENAME disable trigger TRIGGERNAME'. There's no difference in the result, but

disabling seems more elegant. The key point to realize is that all users will be

affected either way.

Tip #2 - You can selectively bypass a trigger

Tip #1 may not seem too useful when you want to do a large number of updates

during the day but are depending on the triggers to handle work done by non

admin users. To selectively bypass a trigger just add a couple lines of code to

the top of the trigger; if HOST_NAME='Somemachine' then return. This

basically makes the specified machine (or machines if you need that much

flexibility) an admin kiosk, one that can perform operations without the

overhead of the triggers.

Tip #3 - Triggers fire per batch, not per row

This surprises many users, but triggers are really a set based operation. If

you run an update that modifies 10 rows, the update trigger would fire once.

This means that any processing you do in triggers that references the logical

inserted or deleted tables must know how to process multiple rows.

Tip #4 - Triggers can return resultsets - but it's not a good idea

A result set will get returned if you have a plain select statement in the

trigger. By plain I mean its just a standalone select, not part of an update or

other operation. Returning a resultset to an application that isn't expecting

one may not cause anything to break, but it could, and more importantly, we

don't want to send data across the network that no one will use. I make it a

practice to use SET NOCOUNT ON as well to avoid sending back the rows affected

message as well. If you want to really make sure no resultsets occur you can use

spconfigure to set disallow results from triggers to on.

Tip #5 - Triggers are logically part of the transaction

Even though we think of them as after triggers anything we do in the

trigger will be treated as part of the transaction. One good way to see this

(and to prevent deletes) is to create a delete trigger that just contains one

statement; rollback.

Tip #6 - Triggers execute with the permissions of the user

A common use of triggers is for auditing. For example, if we have a Contact

table we might create an empty copy of it called ContactHistory and then apply a

delete trigger to Contact that when executed causes any deleted rows to be

inserted into ContactHistory. This will work find when a sysadmin/dbo tests it,

but will fail when a regular user deletes a row as they will not have insert

permission to ContactHistory.

Tip #7 - We can have multiple triggers of the same type on a table

This is primarily a packaging issue. Suppose that we have an off the shelf

application that comes with it's own triggers installed and we need to add some

custom behavior of our own. We can create an additional set of triggers or

modify the ones already there. Adding a new set of triggers is perhaps a tiny

bit more overhead but in return we have nice separation of our code and their

code. The downside is that we can only specify the execution order to a degree

by setting the first and last trigger, any triggers in between have no

guaranteed execution order. Setting first/last triggers is done using

sp_settriggerorder.

Tip #8 - Triggers can be called recursively

Recursion is code elegance at it's best but that doesn't mean we should apply

this technique too often. It's hard to troubleshoot and it makes the duration of

our transactions longer.

Tip #9 - Remember that we have Instead Of Triggers

We can think of these as before triggers so we can use them to check

or change data even before check constraints fire. Even more interesting is that

we can apply instead of triggers to views, giving us the ability to do things

like update what would otherwise by non updatable views.

Tip #10 - In SQL 2005 we also have DDL and CLR triggers

DDL triggers allow us to inspect and act on DDL changes committed on the

server. This might be used just for auditing, or for more complex tasks like

automatically checking changes into source control or enforcing naming

conventions. CLR triggers work much like regular triggers, but with all the

power and caveats that come with using the CLR anywhere within SQL.

Triggers are a great feature of SQL and it's good to think about all the rich

options we have with them. Use them carefully, but use them when they make

sense. I hope these tips prove useful and get you thinking about how you use - or abuse - triggers in your databases.