SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Trigger Trivia

By Andy Warren,

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.

Total article views: 8330 | Views in the last 30 days: 14
Related Articles

Disable and enable trigger

Disable and enable trigger



Is it possible to write a single trigger for UPDATE and DELETE and allow some Delete specific code ?...


Trigger - accessing updated and deleted

Can't bind to updated and deleted tables


Trigger - accessing updated and deleted

Can't bind to updated and deleted tables


Disabling Trigger on Replicated Table

Way of disabling trigger on replicated table