In my last post in this series, I talked about choosing inappropriate data types. This time, I want to touch on a few ways that I see triggers being misused.
Using a trigger at all
A lot of people think that they need
a trigger. They allow direct access to tables instead of forcing data
access through stored procedures, and then later realize that they want to control a modified date column or append rows to a DML audit table. Since they have ad hoc queries in their application code, it is not practical to go out and add additional code to the existing queries.
Ideally, you should control data access via stored procedures, both for security reasons and so that you can control the DML that affects your tables. In a stored procedure, you can apply
conversions to incoming values, and supply data for unspecified
columns, instead of having to deal with it in a trigger. Already using stored procedures to update your table? Great! Just add the modified date column to your UPDATE statement, and you can disable your trigger.
There will be cases where you won't have a choice but to use triggers. You can't always convert to stored procedures, and you can't always prevent certain
jerks people from bypassing your stored procedures and modifying the table directly. Keep in mind that triggers can be disabled by people with sufficient permissions, so it is not your be-all and end-all as an auditing tool.
Not preparing for multi-row updates
A lot of people coming from an Oracle or other background assume that a trigger will fire for every row that is affected. For SQL Server, this is not true; a trigger fires once per DML operation. I often see code examples that look like this:
CREATE TRIGGER dbo.foo_update
ON dbo.foo
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @bar INT;
SET @bar = (SELECT bar FROM inserted);
-- other stuff
END
GO
|
Now, if you do something like:
INSERT dbo.foo(bar) SELECT 1 UNION ALL SELECT 2;
|
You will receive a "subquery returned more than one value" error, and the original update will fail. Oops! You need to code your trigger to handle the inserted rows as a set, instead of expecting the inserted or deleted pseudo-tables to contain exactly one row. There is no straightforward way to immediately convert your thinking to a set-based frame of mind. I will say, if you find that the only way to solve this problem leads to a cursor in your trigger, stop what you're doing, build up a simple demonstration that explains what you are trying to do, and post your question on the SQL Server newsgroups, MSDN Forums or StackOverflow.
Note that there is potentially some confusing trigger behavior in SQL Server 2008 when using the new MERGE command; a trigger that handles multiple operations can fire multiple times. Hugo Kornelis filed a bug on Connect; it was closed as "by design."
Performing inappropriate actions
A lot of times I've gone out of my way to convince people to stop performing quite expensive operations in their triggers. The most common action I have seen is sending an e-mail. I am not sure why people think it is a good idea to send e-mail from within a trigger; since the module is tied to the transaction that caused it to fire, now that transaction has to wait for the return of whatever process you're calling to dispatch the e-mail. Similarly troublesome actions include functionality outside of SQL Server, such as COM / OLE automation objects (sp_OA*), extended procedures, and linked server queries. Anything that takes you outside of SQL Server's direct and local control can become problematic quite quickly.
So what can you do to get around this? Well you can certainly consider using Service Broker, which will allow you to perform any of these actions asynchronously; this allows your trigger to return control to the calling session immediately. As an alternative to Service Broker, you could set up your own simple queue table, and have a background job that runs periodically and checks for any new tasks you've placed on this queue. Again, this means your trigger only has to perform the insert into the queue table, and not force the calling transaction to wait for any subsequent actions that need to take place.
Summary
A trigger is not free, and can lead to various other potential problems in your application. I have touched on a few of them here, but there are several others you will come across if you perform your due diligence. Alex Kuznetsov went over several potential problems with triggers in great detail in his Defensive Database Programming series earlier this year. Personally, I try to avoid them by solving problems in other ways;
however, if you have to have them, it is important to understand some
of the limitations and gotchas they can introduce to your environment.
I am working on a series of "Bad habits to kick" articles, in an
effort to motivate people to drop some of the things that I hate to see
when I inherit code. Up next: using the visual designers in SSMS.