The T-SQL Tuesday subject for September, chosen by Steve Jones (b|t), is to write about an experience I had with triggers. I decided to apply a very loose interpretation of his assignment and write more about triggers in general.

Undeserved hate

My very first SQL Server related job was quite unusual. I was added to a team that developed software using a code generator. We had to define business rules (such as constraints and derivation rules) on top of a conceptual information model for the company. The tool would then generate T-SQL code to support and implement those rules. The generated code used triggers. Lots of them. And it worked.

So even after I moved on to other, more regular SQL Server development work, I never forgot that triggers are very good at doing what they are supposed to do: respond to certain changes no matter where they originate from.  But a lot of people in the SQL Server community have a thorough dislike, perhaps even hatred for trigger. It took me some time to understand where this is coming from.

This post is about the two main reasons why people dislike triggers, and how to alleviate these concerns.

It doesn’t work!

An often heard complaint about triggers is that they suddenly do not work, or do not work correctly, in production. Whenever I get a chance to investigate these situations, I always see that the error is not caused by “triggers” (the concept), but by “that trigger” (or rather, the coding errors in it).

The code below is a typical coding pattern that I have seen far too often and that I hope never to see again:

CREATE TRIGGER dbo.BadExample
ON dbo.Demo
AFTER INSERT
AS
DECLARE @Var1 int,
        @Var2 varchar(20);

-- Find the row that was inserted
SELECT  @Var1 = i.Col1,
        @Var2 = i.Col2
FROM    Inserted AS i;

-- Do something relevant with the row
/* REST OF THE CODE GOES HERE */
GO

A developer can write code such as the above. They will test their code using a front end tool that sends individual single-row insert, update, and delete statements. They will perhaps, if you are lucky, also run a few quick tests from Management Studio – but again using singleton statements. They will then conclude that their code works correctly.
And then the code is deployed to production. And in production, a nightly job runs an import into a staging table, does some cleanup, and then inserts all new rows in a single statement. The trigger will run, correctly. Not correctly as intended, but correctly as written.

You see, a trigger does not run once per row (it does in some other products, but not in SQL Server). When you run an insert, update, or delete statement, a trigger will run exactly once, exposing all affected rows in the inserted and/or deleted pseudo-tables. This can be a single row, or it can be multiple rows, or no rows at all. The trigger needs to cater to all those possibilities. The developer needs to test all those scenarios. When you run a merge statement, a trigger can run up to three times (for inserted, updated, and deleted rows). You also need test cases for these scenarios.

The sample code above would fail some of these tests. The instructions are clear: look at all the rows in the inserted pseudo-table, pick one (more or less at random) to store in the variable, and then ignore the rest. The user will scream that the system malfunctions. The developer will look at their unit tests and claim that it’s SQL Server’s fault for not executing the trigger correctly. In reality, it is the developers fault for not writing and not testing their code correctly.

Unexpected side effects

The other common complaint is coming mostly from DBAs. Their problem is awareness. Or rather, lack thereof.

It is not that triggers are specifically hidden. They are visible in the Object Explorer. They can also be found by querying the DMVs (sys.triggers for DML triggers). However, it is true that they are not exactly in your face.

Suppose you are the DBA on call. You are disturbed in your sleep at 3AM, and you need to do a quick repair of some dirty data to ensure that the nightly batch can continue to run and finish before the maintenance window finishes and the first branches open for business. Are you sure that in your sleepy state you will always remember to click the Triggers tab in Object Explorer? Or can you imagine quickly typing a statement to delete or update a bad row and then firing it off without realizing that you just set off a trigger? And now, perhaps, that trigger has just sent a message on a Service Broker queue that will do all kinds of stuff in the remote CRM system, and send some emails to your client list. Oops….

This is an area where Microsoft can help out. Microsoft can change the Object Explorer so that tables (and views!) with triggers stand out a bit more. Would it be a huge effort to add a red lightning bolt symbol on top of the table symbol, as a warning that one or more active triggers exist for that table? If you would like to see this implemented, then please vote for my suggestion here.

Tool vendors can help as well. I am personally a huge fan of SQL Prompt. One of the nice features this tool offers is to pop up a warning symbol if I am about to execute a statement or a batch that includes an update or delete statement without WHERE clause. Would it be very hard for Red Gate to also implement a warning if I am about to run a statement that would affect a table or view that has an active trigger? Again: if you like it, vote for my suggestion!

Conclusion

Triggers are a great tool. They offer a functionality that is not otherwise available, and that can sometimes work wonders – just think of logging changes for audits, or INSTEAD OF triggers on views that would otherwise not be updatable.

When coded badly, they will fail. That is not a trigger problem. It is a bad code problem, which is often caused by incorrect assumptions on how triggers fire. Just remember, a trigger typically triggers once per statement, for all affected rows. And a MERGE statement is executed as combined INSERT, UPDATE, and DELETE actions, that each have their own trigger action.

It is possible to get unexpected results if you are not aware which triggers exist. That is an issue that developers and DBAs cannot fix. Microsoft and tool vendors can, and I have used their feedback channels to request exactly that.