Re: Reliable way to get ID of inserted record

[Jeff Garza]

I think that Rob nailed it in the posting right after yours.  This is only an issue where you have a trigger that inserts into a different table that has an IDENTITY field in it.  In that case, @@IDENTITY will return the inserted ID from the trigger rather than your your original insert.  Also, as Rob stated, SCOPE_IDENTITY() is only available in SQL Server 2000...

I use it because our DBA has a habit of adding triggers to our tables without our knowledge... been burned once by it before.

As for actual usage, it's pretty much the same as @@IDENTITY:

TABLE DESIGN
========================
pk_ID int IDENTITY (1, 1) NOT NULL ,
othercol varchar(10)
========================
SQL QUERY
========================
SET NOCOUNT ON

INSERT INTO myTable (othercol)
VALUES ('myvalue')

SELECT SCOPE_IDENTITY() AS newID

SET NOCOUNT OFF
========================

This returns queryname.newID to CF...

Cheers,

Jeff Garza
  ----- Original Message -----
  From: Jeff Small
  To: CF-Talk
  Sent: Thursday, March 11, 2004 11:48 AM
  Subject: Re: Reliable way to get ID of inserted record

  From: "Jeff Garza" <[EMAIL PROTECTED]>
  To: "CF-Talk" <[EMAIL PROTECTED]>
  Sent: Thursday, March 11, 2004 1:35 PM
  Subject: Re: Reliable way to get ID of inserted record

  > Be carefull using @@IDENTITY, as it can return erroneous information
  (especially if your tables have triggers associated with them).  If you are
  using SQL Server 2000, use the SCOPE_IDENTITY() function instead of
  @@IDENTITY.

  Oooh! Use it in an example please! And how exactly *can* this produce
  erroneous info?

  Jeff,
  always used @@IDENTITY...and is now scurred...

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]

---