Michael got the extract right. True, books on line doesnt say NOT to
use @@identity, but it does say it's risky if there are several
inserts going on at once.
@@IDENTITY gives you the identity from the last insert in the
database, whereever it was. Which might or might not be the one you
want. If your insert triggers other inserts in other tables, you'll
get the ident of the last insert in the last table of your triggered
inserts. If there are multiple threads inserting elsewhere in the
database, you'll get whatever was the last one, which might be
someone else's insert in a completely different transactoin. If you
use Ident_current('tablename') you get the last insert on the table
you're looking at. It's far more specific and far less prone to
giving you misleading results.
Cheers
Mike Kear
Windsor, NSW, Australia
Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month
On 4/18/05, Robertson-Ravo, Neil (RX)
<[EMAIL PROTECTED]> wrote:
> I cannot see in BOL where it says not to use it (I am on BOL SQL 2000
> (Updated)). I certainly know that @@IDENTITY will give you the last known
> identity for any table / scope.
>
> I don't use SCOPE_IDENTITY much but have done.
>
>
> -----Original Message-----
> From: Mike Kear [mailto:[EMAIL PROTECTED]
> Sent: 18 April 2005 14:10
> To: CF-Talk
> Subject: Re: Last ID inserted
>
> Dont use @@Identity. Even the books on line (the online help for
> SQLServer) says it's risky in many circumstances. That was from an
> earlier version of SQLServer. The correct function to use is either
> Scope_identity() or Ident_current() .
>
> Dont take our word for it, read the books online entry. It seems the
> more we say DONT use @@Identity, the more people say DO use
> @@Identity.
>
> It's not MY words saying dont use it, its MICROSOFT's own help file
> that says @@Identity has risks of giving the wrong answer, especially
> when you have many inserts going on simultaneously.
>
> There'll no doubt be lots of people saying "well I've been using
> @@Identity and it's been fine for me". And the answer to that is
> you're lucky. Read Books on line about it.
>
> According to books on line, the correct query to use is:
>
> <cfquery name="insertquery" datasource="#application.dsn#">
> SET NOCOUNT ON
> INSERT into myTable (
> fieldname1,
> fieldname2,
> fieldname3)
> VALUES (
> '#form.fieldname1#',
> '#form.fieldname2#',
> '#form.fieldname3#')
> SELECT Ident_Current('myTable') as NewID
> SET NOCOUNT OFF
> </cfquery>
>
> <p>New Identity is #insertquery.NewID#</p>
>
> Cheers
> Mike Kear
> Windsor, NSW, Australia
> Certified Advanced ColdFusion Developer
> AFP Webworks
> http://afpwebworks.com
> ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month
>
> On 4/18/05, Stuart Kidd <[EMAIL PROTECTED]> wrote:
> > Thanks for all your help on that everyone, the responses more than
> answered my question! :)
> >
> > When i get home i will put it into my code.
> >
> > Saturday
> >
> > ----------------------------------------
> > From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]>
> > Sent: 18 April 2005 07:42
> > To: CF-Talk <[email protected]>
> > Subject: RE: Last ID inserted
> >
> > Triggers...?! ;-) evil evil evil...
> >
> > -----Original Message-----
> > From: JediHomer [mailto:[EMAIL PROTECTED]
> > Sent: 18 April 2005 12:36
> > To: CF-Talk
> > Subject: Re: Last ID inserted
> >
> > Except that the @@IDENTITY is the last inserted ID, if you have any
> > triggers setup that insert then this could be affected,
> > Scope_Identity() locks the ID getting to the current scope/view so
> > remains unaffected by triggers etc
> >
> > On 4/18/05, Robertson-Ravo, Neil (RX)
> > wrote:
> > > SELECT @@IDENTITY is all you need.
> > >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203208
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
