See the full index.
Today, 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, or roll back on some condition. Since they have ad hoc queries in their application code, it is not always practical to go out and add additional code to 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 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 DECLARE @bar int; SET @bar = (SELECT bar FROM inserted); -- other stuff with @bar END GO
Now, if you do something like:
INSERT dbo.foo(bar) VALUES(1),(2);
You will receive a "subquery returned more than one value" error, and the original update will fail. Oops!
CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN DECLARE @bar int; SELECT @bar = bar FROM inserted; -- other stuff with @bar, e.g. SELECT @bar; END GO
This will work fine for a single-row update. A multi-row update won't fail; only one (arbitrary) row will be displayed, because only one value can be assigned to the variable. But you won't be able to reliably predict which one (and SQL Server won't raise a warning, an error, or any kind of notification that it ignored the other rows). Here is an example of this "failing":
CREATE TABLE dbo.foo(id int PRIMARY KEY, score int, updated date); INSERT dbo.foo(id,score) VALUES(1,10),(2,20),(3,30); -- all three rows have NULL for updated GO CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN DECLARE @foo_id int; SELECT @foo_id = id FROM inserted; UPDATE dbo.foo SET updated = '20200101' WHERE id = @foo_id; END GO UPDATE dbo.foo SET score += 1 WHERE id IN (1,2); GO SELECT * FROM dbo.foo; /* results: id score updated ---- ------- ------------ 1 11 2020-01-01 -- this row was updated, and the trigger also updated it 2 21 NULL -- this row was updated, but the trigger didn't touch it 3 30 NULL -- this row wasn't touched by the update or the trigger */
You need to code your trigger to handle the affected rows as a set, instead of expecting the inserted or deleted pseudo-tables to contain exactly one row. Typically you do this using a query or join against inserted/deleted instead of variable assignment. In this example:
CREATE TRIGGER dbo.foo_update ON dbo.foo FOR UPDATE AS BEGIN UPDATE f SET updated = '20200101' FROM dbo.foo AS f INNER JOIN inserted AS i ON f.id = i.id; END GO
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 Database Administrators Stack Exchange.
MERGE WITH CAUTION
Note that there is potentially some confusing trigger behavior in SQL Server 2008 when using the 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." For a lot more on the problems with MERGE and why I try to block all usage:
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 (even calls that are quasi-asynchronous, like database 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 quickly become problematic.
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. See these posts from Jonathan Kehayias: Part 1 and Part 2. As an alternative to Service Broker, you could set up your own simple queue table (see guidance from Remus Rusanu), 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.
Triggers are 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.
See the full index.