Stop trigger changing scope_identity

scope_identity returns null
how to get last identity value in sql server
how to use scope_identity in insert statement
identity vs scope_identity
scope_identity in sql server w3schools
sql server get identity value after insert
identity in sql server
print 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

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 |

SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. Failed statements and transactions can change the current identity for a A. Using @@IDENTITY and SCOPE_IDENTITY with triggers. SCOPE_IDENTITY() returns the value from the insert into the user table, whereas @@IDENTITY returns the value from the insert into the replication system table. Use SCOPE_IDENTITY() for applications that require access to the inserted identity value.

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.

Identity and Scope_Identity with triggers – Learn more on the END. Now we added this archive database and also created triggers for it on this If you keep the architecture the same, change the trigger to an insert trigger. This trigger inserted records into another table and SCOPE_IDENTITY() returns the last ID of the inserted record on that other table when inserting into the primary table. Was driving me insane because it was working for other tables and just not this one specifically.

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')

These two changes lead to some additional concepts SCOPE_IDENTITY(), as the name trigger can stop the operation dead in its tracks. Animations applied by property Trigger objects behave in a more complex fashion than EventTrigger animations or animations started using Storyboard methods. They "handoff" with animations defined by other Trigger objects, but compose with EventTrigger and method-triggered animations. See also. Trigger; Property Animation Techniques Overview

This may help anyone in the future with this issue:

https://docs.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.

SCOPE_IDENTITY() returns NULL, since the insert actually happened in a the INSTEAD OF INSERT trigger could now (or might later be changed to) insert Immediately it's clear that a transaction isn't enough to prevent  Being a warrior inside my own life has meant looking at the things that trigger me, anger and frustrate me, wound me, hurt me and depress me. It doesn’t sound like fun, but I’m here to tell you it’s life-changing to choose this journey of healing. Every step brings me closer to my purpose, a true feeling of what I was born to do.

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/

Hello, Issue The query doesn't return scope_identity if i have some @CarId END go INSERT INTO Cars (Name) SELECT 'Ford' UNION  A buy-stop order is a type of stop-loss order that protects short positions and is set above the current market price triggering if the price rises above that level.

SELECT SCOPE_IDENTITY() as ID; So, I change to: Stack overflow link: https://stackoverflow.com/questions/15883304/stop-trigger-changing-scope- @​stevebauman did you find an alternative? i cant change triggers  With a traditional stop loss order, say you have a $15 stock. You establish a sell level (say, $10) that will not change. If the price of your stock goes up to $20, you still have a $10 sell level. If the stock plummets, you will still sell at $10. With a trailing stop loss order, say you have a $15 stock. You might establish a trailing stop

For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements will not see a changed​  SIG SAUER P228 P226 P220 CARBON SLIDE INNER and OUTER SLIDE PINS - NEW. New Mainspring For P220, P226, P228, P229, P245. New Hammer Reset Spring For P225, P226, P228, P229. New Slide Stop Lever Spring For P226, P228, P229. New Decocking Lever Spring For P220, P226, P228, P229. New Magazine Release Spring For P220, P226, P228, P229.

SCOPE_IDENTITY returns the last IDENTITY value inserted into an END 3. Do a select statement of both the tables: SELECT * FROM Table1 It will consider identity value changed by trigger as trigger is another scope. Occurs when cells on the worksheet are changed by the user or by an external link. expression. Change ( Target) expression A variable that represents a Worksheet object. Required/Optional. The changed range. Can be more than one cell. This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet

Comments
  • If you used @@identity then that would indeed be the case but since you are using scope_identity that should not happen.
  • I have been informed that it is scope_identity, but have not confirmed so. Whichever it is, I am sure that it is changing.
  • @MikaelEriksson nailed it. When you say "the application tries to look for scope_identity" what does that mean? How/where/when is it getting the scope identity?
  • As I said above, I do not have access permissions to see the code, at the moment, all I have been told is that it uses scope identity.
  • You could use SQL Profiler and have look at what the client does.
  • Thanks. THis was useful, i wasnt aware that you could select the identity at different points. I did this, and the identity always comes back as the expected one (the id from the first table). I think this is going to be related to the bug metnioned above
  • Great suggestion - I used a temporary table in my trigger and it worked (which surprised me because I wondered if the scope of a temporary table would last long enough). if (select count(1) from inserted) = 1 begin create table #TempIdTable (ResetIdentity int identity(1, 1)) set identity_insert #TempIdTable on insert into #TempIdTable (ResetIdentity) select UserID from inserted set identity_insert #TempIdTable off end
  • @MartinSmith Incorrect, I have the same exact issue and creating a temporary table with an insert into it as the last statement in the trigger using SCOPE_IDENTITY() worked.
  • It's been about 5 years since I posted so I can't really test but this does seem to be the answer
  • @Wayneio Yeah I'm on SQL Server 2012 and it seems to have been patched in a later release. Took me hours to come to your stack issue with your resolution to fix it :P
  • This would have to be placed in the application code, after the trigger. However I only have access to the trigger, so I need to stop the trigger actually changing the scope_identity.
  • If you have multiple clients adding rows at the same time using IDENT_CURRENT() is not such a good idea. You may get the identity value generated by another client.