How to get Identity value whith help of scope_identity()

Question

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?

Answer 1

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.

Answer 2

First problem: You're writing your trigger with the assumption that only one row will be inserted. What happens if multiple rows are inserted by a single statement?

There is a special inserted table available in triggers that contains all of the rows inserted by the operation that caused the trigger to fire. You could use it this way:

INSERT INTO Table_Temp
    (TempID)
    SELECT i.YourIdentityColumn
        FROM inserted i