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.
> >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:203197
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
