CODEWITHSUNDEEP
sqlsql-servertriggerssql-server-2008-r2
Wayneio
Wayneio

Reputation: 3566

Stop trigger changing scope_identity

My application inserts some data into a table.

insert into this_Table (id, value, value)

I have then created a trigger that does a simple insert, into a different table with a primary key. 

insert into temp_wc_triggertest values ('test', GETDATE())

My problem is then, the application tries to look for scope_identity, from the first insert. However, it gets overwritten by the trigger, which changes the scope identity to the primary key of temp_wc_triggertest.

How can I stop the trigger overwriting scope_identity?

I realise there is not much code to help here, which would normally be classed as a bad question, but I don't have permission to access to the full application code at the moment, so I am hoping this is answerable as is.

This is on SQL Server 2008 R2

EDIT: I have looked at the code, and it does use scope_identity

Upvotes: 5

Views: 4832

Answers (5)

Steve Bauman
Steve Bauman

Reputation: 8688

This may help anyone in the future with this issue:

https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017

You may need to patch SQL Server, as it looks like the SCOPE_IDENTITY should return the inserted ID on the table that is actually receiving the primary insert, not the SQL trigger insert statements.

From the Microsoft docs for SQL Server 2017 (in case link is broken):

Remarks SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Another link here:

http://www.sqlbadpractices.com/how-not-to-retrieve-identity-value/

The problem with this code is that you may not retrieve the identity value that you inserted. For example, if there is a trigger on the table performing an insert on another table, you will get the last created identity value. Even if you never create any trigger, you may get skewed results with replicated tables since SQL Server creates his own replication triggers.

Upvotes: 0

Wayneio
Wayneio

Reputation: 3566

The reason for it overwriting scope identity is still not clear, it could possibly be related to the bug mentioned. However a fix was found:

A temporary table was created "temp_wc"

Then at the end of the trigger, identity insert was switched on for that table, and an insert was done, for the ID that we want to keep after the trigger has fired. This method can be thought of as overwriting the overwritten scope identity again.

SET IDENTITY_INSERT ON
INSERT INTO temp_wc VALUES (@ID, 'fix scope identity error')

Upvotes: 2

StingyJack
StingyJack

Reputation: 19469

If you are using SCOPE_IDENTIY correctly, you may also be experiencing a known bug - http://connect.microsoft.com/SQLServer/feedback/details/328811

MS has fixed it permanently for 2012, and has patches available for 2008 and 2008R2.

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Your client is most certainly using @@IDENTITY instead of SCOPY_IDENTITY()

Here is a SQL Fiddle with some code you can test on.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table T1(ID int identity(1,1));
create table T2(ID int identity(1000, 1));

go

create trigger tr_T1 on T1 for insert
as
insert into T2 default values;

Query:

insert into T1 default values

select @@identity as "@@identity", 
       scope_identity() as "scope_identity()"

Results:

| @@IDENTITY | SCOPE_IDENTITY() |
---------------------------------
|       1000 |                1 |

Upvotes: 4

Brad M
Brad M

Reputation: 7898

Use SELECT IDENT_CURRENT(‘tablename’)

"It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value."

See this link for details.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Upvotes: -2

Related Questions