Search code examples
sqltriggersscope-identity

How to get Identity value whith help of scope_identity()

I have scope_identity() that implemented in TRIGGER. 

ALTER TRIGGER TempProcTrig
ON Table_temp2
AFTER insert
AS
BEGIN

     declare @TempIdentity int
     set @TempIdentity =scope_identity() 
     insert Table_Temp(TempID) 
     values(@TempIdentity)
END

When TRIGGER is fired @TempIdentity gets Identity column field, and set this value into another table.

But, always after TRIGGER is fired, @TempIdentity gets NULL.

Why TempIdentity dosen't get Identity field? What should I change in my code?

Solution

  • The cause of this issue is that, as the name implies, SCOPE_IDENTITY() only returns the id value from the scope that you call it in.

    Calling it from a trigger will return the identity value from within the trigger, which is NULL since you aren't inserting any rows.

    As Joe suggests, use the inserted table to get the values needed instead of this function.