SQL: Real triggers, SET NOCOUNT ON, and Counting Correctly

I’ve done a lot of Microsoft exams over the years, mostly SQL Server ones but plenty of others too. And one thing that I really don’t like is when the questions are:

• Purely academic (ie: would never happen)
• Memory based (ie: who cares what the DTU limit is for a P3 today?)
• Clearly not written by someone who actually uses the product

Today, I want to mention an item in the last category.

In many exams, I’ve seen T-SQL code for triggers and one of the instant tell-tale signs of whether the author of the question really uses triggers or has just read the documentation and thinks they know about triggers, is whether or not they’ve used SET NOCOUNT ON.

If an application updates two rows in a table, SQL Server tells it that two rows were updated.

If you then add a trigger that, for example, updates a row in another table, SQL Server will now tell the application that three rows were updated.

This is enough to break many applications.

The application that sent the update won’t want to know about other rows inserted, updated, deleted, etc. by your trigger. That will only confuse or break things.

While there are odd exceptions where you do want the default behavior, the answer to this is to use SET NOCOUNT ON. When we are teaching students to write triggers, we tell them that nearly every DML trigger should start with SET NOCOUNT ON. Check out the documentation here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql

SET NOCOUNT ON stops the count of affected rows being returned for an operation. The beauty of using this in a trigger is that it stops the count of rows affected by the trigger from affecting the count that the application sees.

So, when I see a whole lot of exam questions on triggers and none of them include this, that’s really disappointing in so many ways. Learn to use this in your trigger code.

(Note that we try to avoid using triggers in the first place most of the time).

2018-02-26