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.
