How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000Fri, Dec 23, 2005 in Databases
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.
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:
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.
I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and many open-source tools for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB.