How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000

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.

Silly rowcount/identity graphic

To quote the documentation:


Returns the number of rows affected by the last statement.

Couldn’t be much simpler. Getting the last IDENTITY value is not as straightforward though. There are several options: @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT(). You can read up on the differences, but the one you probably want to use is SCOPE_IDENTITY(). It gives you the last value in the current scope, which means it won’t be affected by triggers or other connections. The other two methods could give bizarre results depending on what else is going on in the database. They have their uses, but only in specialized cases.

See Also

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.