Udostępnij za pośrednictwem

Scope_Identity() and OUTPUT

  • Artykuł

I seen couple of articles talking about OUTPUT Clause of SQL 2005, but no one is warning the side affect of it on the scope_Identity() function. Let me explain with an example:

You have a Table T1, and would like to save the auditing data from T1 to T2. To accomplish this you define a trigger on T1 which will insert the T1 data in T2.

create table T1

(

T1_ID int identity,

FirstName char(50),

SecondName char(50)

)

CREATE TRIGGER T1_Insert

ON T1

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO T2 (FirstName, SecondName) SELECT INSERTED.FirstName, INSERTED.SecondName FROM inserted

END

create table T2

(

T2_ID int identity,

FirstName char(50),

SecondName char(50)

)

INSERT INTO T1 (FirstName, SecondName) values ( 'FirstName', 'SecondName')

SELECT SCOPE_IDENTITY()

Above statement return you the identity inserted on T1, and this is what exactly you wanted. Trigger has inserted the data in T2 but scope_identity returned you the identity of T1. So far so good, Every one is happy.

Now you find this OUTPUT Clause in SQL 2005, and change your code as follows. You deleted the trigger and write the insert statement as follows:

INSERT INTO T1 (FirstName, SecondName)

OUTPUT Inserted.FirstName, Inserted.SecondName

INTO T2 (FirstName, SecondName) values ( 'FirstName', 'SecondName')

SELECT SCOPE_IDENTITY()

Now in this case you will get the identity of T2.

Quiz: So, what one should do?

Comments

  • Anonymous
    March 13, 2007
    Why wouldn't you use IDENT_CURRENT('T1') in this case?

  • Anonymous
    November 15, 2007
    In this case, you can reverse the two: INSERT INTO T2 (FirstName, SecondName) OUTPUT Inserted.FirstName, Inserted.SecondName   INTO T1 (FirstName, SecondName) values ('FirstName', 'SecondName') SELECT SCOPE_IDENTITY() but that won't always be possible.