Scope_Identity() and OUTPUT

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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s