SET NOCOUNT ON resets @@ROWCOUNT

24. February 2009
Comments (3)

I ran into an interesting problem while writing an update trigger in SQL Server 2008. I right-clicked on the triggers folder for a table and selected "New trigger". Now the new trigger template adds "SET NOCOUNT ON;" at the beginning. Normally it is a good practice so that the rows affected message doesn't interfere with select statement results.

...

  

  SET NOCOUNT ON;

  IF(@@ROWCOUNT) = 1

  BEGIN

...

The problem I encountered is that I was checking the @@ROWCOUNT variable to see if only one row had been affected and by having the NOCOUNT statement before my if statement the @@ROWCOUNT was being reset to 0.

The solution was simple, I just moved the NOCOUNT statement before my UPDATE statement in the trigger. 

 

BlogEngine.NET

Comments

Free Service Manual
Free Service Manual
4/1/2009 9:21:22 AM #
Thanks for the great reference post
CJ
CJ
6/11/2009 3:07:39 PM #
I've run into this problem in SQL Express 2005 sp3. I didn't think this happens in previous versions. I'm going to do the same thing as you did, but I appreciate knowing that someone else has seen this same issue.
ralmlopez
ralmlopez
6/22/2009 11:30:22 AM #
Glad I was able to help.