Ectropy Ectropy - 2 months ago 9
SQL Question

Is is safe to use @@IDENTITY in a transaction?

I was reading this answer about different methods of getting the last identity value entered into a database.

From what I understand,

@@IDENTITY
is usually a very bad idea because it might return an identity that is not the one you expected--for example an identity value that was recently created by a trigger.

But what if your code is in a transaction?

For example this is a simplified version of a transaction I'm doing (using ColdFusion):

<cftransaction>
<cfquery name="queryInsertA" datasource="source">
INSERT INTO tableA (columnName) VALUES (value)
</cfquery>
<cfquery name="queryInsertB" datasource="source">
INSERT INTO tableB (fkey_tableA, columnName) VALUES (@@IDENTITY, value)
</cfquery>
</cftransaction>


Since, "If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database," does this mean that it would also prevent the isses that can arise when using
@@IDENTITY
? Or am I misunderstanding the behavior of transactions?

Answer

The answer you linked already explains what the main issue is with @@IDENTITY: scope. If your insert triggers another insert, you get an unexpected identity back. Transactions do not change anything.