How to obtain the inserted id value in SQL Server

In actual database applications, we often need to get the inserted flag value to write data into the relevant table. But what we usually get is the value we need?

Sometimes we use

SELECT @ Identity

To obtain the value we just inserted, such as the following code

Code 1:
Use tempdb
If exists (select * from sys. objects where object_id = object_id (n' [test1] ') and type in (n'u '))
Drop table [test1]
Go
Create table test1
(
Id int identity (1, 1 ),
Content nvarchar (1, 100)
)
Insert into test1 (content)
Values ('solorez ')
Select @ identity

In optimistic situations, this is fine, but if we first run the following code 2 to create a trigger and then run code 3:

Code 2:
Create table test2
(
Id int identity (100,1 ),
Content nvarchar (1, 100)
)

Create trigger tri_test1_identitytest_ I
On test1 after insert
As
Begin
Insert into test2
Select content from inserted
End

Code 3:
Insert into test1 (content)
Values ('lorez2 ')
Select @ identity

We can see that the resulting id value is more than 100. Obviously, this is the ID value generated in Table test2, and it is no longer what we want.

Let's take a look at the definition of @ identity: Identity

Originally, @ identity returned the id value inserted at the end of the current transaction.

In this case, we may use the following method:

Code 4:
Insert into test1 (content)
Values ('lorez3 ')
SELECT IDENT_CURRENT ('test1 ')

It seems that the result is still correct, but if we run code 4 multiple times and run the following code 5 at the same time:

Code 5:
Insert into test1 (content)
Values ('lorez3 ')

Waitfor delay '00: 00: 20'
SELECT IDENT_CURRENT ('test1 ')

The results are not what we want!

Let's take a look at the definition of IDENT_CURRENT (Tablename): IDENT_CURRENT (Tablename)

Returns the final id value of the specified table.

It is time to show the answer. We can use the following code:

Code 6:
Insert into test1 (content)
Values ('lorez3 ')

SELECT scope_identity ()

At this time, whether we add a trigger or run parallel insert, we always get the id value of the current transaction.

Scope_identity () Definition: scope_identity ()

 

PS: this is a problem found when an error occurs in a stored procedure when a trigger is added. I feel that it is universal and I hope it will help you.

References:

Http://msdn.microsoft.com/zh-cn/library/ms187342.aspx

Http://msdn.microsoft.com/en-us/library/ms175098.aspx

Inside SQL Server 2005-Storge Engine

Http://www.cnblogs.com/suchenge/articles/848844.html