In a web environment, if you've got any pages that add anything to the
database, you're in dangerous territory using @@IDENTITY. It returns
the identity of the last record entered, which might or might NOT be
the one you just entered.
You can use SCOPE_IDENTITY() which returns the last item entered in
the scope of the database you're working in but which still might not
be the table you're interested in, if you have a lot of activity on
your database. The best to use (at least in SQLServer2000 anyway) is
the last identity created on the table you are inserting into. If you
still have it locked by putting your identity call in the same query,
you are guaranteed to get the identity of the record you just entered.
The way to do it is:
<cfquery name="insert" datasource="#request.DSN#">
SET NOCOUNT OFF
INSERT into TABLENAME
(field1, field2, field3)
VALUES
('#form.field1#', '#form.field2#', #form.field3#')
SELECT IDENT_CURRENT('tablename') AS ordernumber
SET NOCOUNT ON
</cfquery>
Now the value #insert.ordernumber# will be the identity of that
inserted record. Guaranteed.
The "Set NOCOUNT OFF" allows you to have more than one database
operation in the same CFQUERY because it supresses the SQLServer from
returning messages to the query. They'll break the query if they
appear midway through the operation.
Cheers
Mike Kear
Windsor, NSW, Australia
AFP Webworks
http://afpwebworks.com
.com,.net,.org domains from AUD$20/Year
On Fri, 10 Dec 2004 19:23:18 -0800, Joe Rinehart <[EMAIL PROTECTED]> wrote:
> Aye, you'll want to do SCOPE_IDENTITY() 95% of the time. And only do
> the other 5% if you know why you're doing it that way :).
>
> @@Identity looks across all tables, so if your insert has fired a
> trigger that inserts into another table, you'll get the identity from
> the second table (that the trigger inserted into), not the first.
> Oops!
>
> There's also IDENT_CURRENT(tablename) which looks tempting because it
> "returns the identity of the last row enterered into the specific
> table." This should probably be avoided in a multi-user system, as
> it's possible for someone to do another insert between your INSERT
> statement and SELECT IDENT_CURRENT(tablename) AS... statement.
>
> -joe
>
>
> On Fri, 10 Dec 2004 16:51:04 -0400, Kwang Suh <[EMAIL PROTECTED]> wrote:
> > It has nothing to do with threads; rather if there's a trigger on the
> > table, the trigger might result in you getting the incorrect ID.
> >
> > Always use SCOPE_IDENTITY() if you're using SQL Server 2000.
> >
> > Never use triggers if you need to use @@IDENTITY in SQL Server 7. :)
> >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - CFDynamics
http://www.cfdynamics.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187172
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
