Does @@RowCount reflect the number of rows affected by the last statement within the current scope, or simply the last statement?

As an example, if I update a row in a table which has a trigger associated with it, is the value returned by @@RowCount the number of rows updated by my statement or by the trigger?

---

If beauty sits the child's kiss of laughter I amend,
Can you catch her if she runs?
With this I would share with you,
All of this come to no end

You are safe to use @@ROWCOUNT in SQL Server 2000, even where there are Triggers around. It is tied to the scope, and the trigger operates in a different scope, just as does Dynamic SQL.

@@ROWCOUNT Returns the number of rows affected by the last statement. @@IDENTITY can be a bit of a minefield though. There are a number of variations, including @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT() of which SCOPE_IDENTITY() normally does what you'd expect.

Here is a little bit of SQL that illustrates the points. (it uses the wordList which is on the BLOG entry 'The Fireside Fun of Decapitations'

CREATE TABLE word

(

  ID      INT       NOT NULL IDENTITY,

  TheWord CHAR (25) NOT NULL

)

GO

CREATE TABLE Summary

(

  ID        INT       NOT NULL IDENTITY,

  Wordcount INT       NOT NULL

)

GO

CREATE TRIGGER tri_word ON word AFTER INSERT

AS

INSERT Summary (Wordcount) VALUES (@@ROWCOUNT)

GO





INSERT word

(

  TheWord

)

SELECT

  word

FROM

  wordlist

WHERE

  word LIKE 'a%'

GO

SELECT  @@rowcount                    'ROWCOUNT'

SELECT    @@IDENTITY                 'IDENTITY'

SELECT    SCOPE_IDENTITY ()          'SCOPE IDENTITY'

SELECT    IDENT_CURRENT ('Summary')  'Summary'

SELECT    IDENT_CURRENT ('word')      'word'

/*

ROWCOUNT    

----------- 

3276



(1 row(s) affected)



IDENTITY                                 

---------------------------------------- 

2



(1 row(s) affected)



SCOPE IDENTITY                           

---------------------------------------- 

6552



(1 row(s) affected)



Summary                                  

---------------------------------------- 

2



(1 row(s) affected)



word                                     

---------------------------------------- 

6552



(1 row(s) affected)





*/

Thanks, I found out about @@Identity, Scope_Indentity() etc recently, which sparked a few thoughts about the scope of @@RowCount.

Glad I don't have to go and change a whole lot of code now.

Thanks again1

---

If beauty sits the child's kiss of laughter I amend,
Can you catch her if she runs?
With this I would share with you,
All of this come to no end

As an example, if I update a row in a table which has a trigger associated with it, is the value returned by @@RowCount the number of rows updated by my statement or by the trigger?

 

Ok I should have read the question more carefully and spotted @@rowcount rather than @@identity. But I'll leave this non-sequitur here as I might want to copy it somewhere later. It contains a comment about the output clause which surprisingly few people seem to have noticed.

 

It will be that returned by the last statement with the caviate that I believe a trigger doesn't set it to null as it ought.

If you insert into a table with an identity in a trigger then @@identity will reflect the identity value from the trigger insert, scope_identity will reflect the value from the firing statement.

If you insert into a table without an identity then @@identity is set to null. If you insert into a table without an identity in a trigger then @@identity is unaffected i.e. in the trigger and after it reflects the identity value from the firing statement - I'm pretty sure that this was cahnged in a service pack some time ago (maybe v7 sp2? and before that it did set the value to null - it's always worth checking).

With v2005 you have an output clause which you can use to return the identity value from tyhe inserted table. Not very helpful for single row inserts but very useful for batch inserts to return all the identities allocated.