Scope of @@rowcount?

Question

What is the scope of @@rowcount ?MSDN doesn't mention its scope.

Returns the number of rows affected by the last statement.

Ok.

In my SP — I'm inserting into a table , which has a insert trigger which does another insertion when a new value is inserted to the table.

Question:

To which scope the @@rowcount will refer ? (trigger or SP) ?

Answer 1

Granted, the article is for SQL Server 2000, but one would hope the scope doesn't change between versions. According to the article How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000, @@ROWCOUNT will not be affected by triggers.

Specifically:

It’s safe to use @@ROWCOUNT in SQL Server 2000 even when there is a trigger on the base table. The trigger will not skew your results; you’ll get what you expect. @@ROWCOUNT works correctly even when NOCOUNT is set.

So if you update three rows, and the trigger updates five rows elsewhere, you'll get a @@ROWCOUNT of 3.

Also, from GBN's answer in SQL Server - is using @@ROWCOUNT safe in multithreaded applications?:

@@ROWCOUNT is both scope and connection safe.

In fact, it reads only the last statement row count for that connection and scope.

Answer 2

every insert/update/select/set/delete statement resets the @@rowcount to the rows affected by the executed statement

begin declare @myrowcount int,     @myrowcount2 int insert stmt SET @myrowcount=@@rowcount if @myrowcount>0 begin insert stmt SET @myrowcount2 =@@rowcount if @myrowcount2 >0 do smthg end end 

or try this

 SELECT * FROM master.sys.objects -- 50 rows     IF (1=1)         SELECT @@ROWCOUNT AS RowsAffected -- 0, because the IF did not affect any rows 

even an IF statement affects it....hence its scope is the last statement read.