Mixed Blessings of SQL Triggers

Sometime we take over a project where the original developer used SQL Triggers on the tables and unfortunately, we tend to find that more often, the triggers are not written in most effective manner. I’d like to highlight few common mistakes I see made with using the triggers:

Assuming only one row

I can’t cite number of times I had to perform bulk data operations only to have it fail or generate unexpected results because of a trigger that couldn’t handle the bulk data operation. That is, the developer simply assumes that there are going to be only single row being inserted, updated or deleted. That is very bad assumption. Sometime the original developers might put in a check like:
IF ((SELECT COUNT(*) FROM inserted) = 1
>do the thing<


Which is OK but that brings me to the next point…

Defining triggers that should fire only sometimes

if it’s only for single-insert/update did it really have to be a trigger? Why not put it in a Access’ AfterUpdate event behind the form or a stored procedure? Triggers incur some overhead and limiting it to only single-row operation misses out on the value of trigger and better approach for managing the logic. Believe me, you really don’t want a trigger that is applicable only sometimes. The trigger will always fire and for business rules that are applicable only sometimes, I think there are better solutions.

Using cursors or procedural programming in a trigger

Let me make it absolutely clear. SQL Server is optimized for set-based operations, not procedural operations. The temptation is great to write T-SQL in the same way we write VBA or C# or whatever programming language you use. Don’t. What you know about your favorite programming language does NOT apply to SQL. Using cursors enables you to write T-SQL a bit more procedurally but it’s akin to hammering nails with a wrench. You’ll get there eventually but the results won’t be beautiful. The key thing about triggers in SQL Server is that it’s always statement-based, meaning all rows that are being operated are affected once. This is why we have inserted and deleted tables, and not a single row constructor. Some other RDBMS products supports row-based triggers but for whatever reasons, Microsoft has declined to support this kind of construction. In many cases, it shouldn’t matter — you want your triggers to run as fast as it can and that means being able to deal with the inserted and deleted like what they are — tables to be operated upon on as a set. Many times, it is possible to rewrite a trigger that would only handle single row into a version that will work for unlimited numbers of rows, removing the IF construct. That’s a big win in performance.

But what if I only need it sometimes and I can’t do it in the client?

There are a number of cases where we have to enforce business rules and they may not be always applicable. The answer is to define a trigger on a view instead of the base table. You can create an INSTEAD OF trigger upon a view and expose that view to the client. Because the trigger is related to the view, manipulating the base table won’t cause the trigger to fire which is both good and bad.

But one biggest plus in my book for defining a trigger on a view is this— I always write my view definition like this:

CREATE VIEW dbo.vwMyView AS
-- IMPORTANT: There is a trigger(s) defined on this view
-- If the view is altered, check the trigger to ensure
-- it is consistent with the new view definition
SELECT
aColumn,
otherColumn
FROM dbo.aTable;

Now I have better reassurance that if I have to revisit view next year or if someone else comes and read my T-SQL definition, they won’t be unpleasantly surprised by trigger lurking in the shadows. Try to do that with a table trigger. It’s not that easy to document or discoverable especially since we typically do an ALTER TABLE that won’t contain the complete definition as an ALTER VIEW does. It’s not bulletproof but much better than it is with table triggers.

In the end, use it sparingly
One unfortunate consequence of creating triggers is that they cost some more maintenance. Make one too many triggers, you may end up with a database schema that’s more fragile than a house of cards. Sometimes it’s just that business rules are best enforced in the application level or at least via a stored procedure. Done right, they can be a boon and simplify your development. Just be careful with the double edges.

Happy selecting!