Differences between SQL Server 2000 @ identity, scope_identity (), and ident_current

I encountered a problem today, and finally found that it was caused by insufficient understanding of @ identity, scope_identity () and ident_current. Write it down and give a prompt to new users like me.

There are two tables Table1 and Table2 at first. The Stored Procedure pro1 implements this logic. After inserting a row of data into Table1, use @ identtity to get the value just inserted in the identity column and insert Table2, always running normally.

Later, due to business changes, table3 and table4 were added. Therefore, Table1 defines an insert trigger. When Table1 inserts a row, the trigger inserts a row in table3 and scope_identity () obtain the value of the newly inserted identity column in table3, and then insert the value of this identtity column into table4;

Then the problem occurs. After each stored procedure pro1 is executed, the data inserted in Table1, table3, and table4 is normal, but the data inserted into Table2 is not the value of the identity column of Table1, the value of the identity column in table 3.

Finally, I found that the @ identity and scope_identity () are different (there is no need to get two items if there is no difference. It was dizzy at the beginning, haha ); both are used to return the identity value.

Scope_identity ()

Returns the last identity value generated for any table in the same scope of the current session.

@ Identity

Returns the last identity value generated by inserting the current session to the intent table (not limited by scope)

Ident_current

Returns the last identity value generated for a specific table in any session and scope (not limited by the session and scope, but limited to a specific table)

 

Scope: a module is a scope. A stored procedure, a trigger, a batch processing, or a function is an independent scope.