Should I add SET NOCOUNT ON to all my triggers?

Question

It is fairly common knowledge that you should have

SET NOCOUNT ON   

by default when creating new stored procedures.

Microsoft has changed the default template to include this in 2012. I thought this should be the same for triggers, yet it is not included in the template.

Is this intentional or just an oversight?

Answer 1

Personally, I would recommend it - I can't think of a reason not to, unless you have a trigger where you specifically want to call out the fact that it's doing additional work behind the scenes.

I wrote a short article about things to watch for when writing triggers, and this is one of them:
http://dave.brittens.org/blog/writing-well-behaved-triggers.html

tl;dr version:

1. Cut the chatter. Use NOCOUNT.
2. Make sure your trigger can handle multiple rows.
3. Prevent unbounded trigger cascading and recursion.
4. Avoid horrible performance of the INSERTED and DELETED virtual tables.