Best to avoid triggers full stop. -----Original Message----- From: Jeff Garza [mailto:[EMAIL PROTECTED] Sent: 01 February 2005 15:41 To: CF-Talk Subject: Re: EASY: grabbing the id of a newly created item..
If you are using SQL Server 2000, I would recommend moving away from using @@IDENTITY as it can return incorrect information if you have a trigger set up on the table you are inserting into. It will return the ID from the Trigger action rather than the newly inserted row. SCOPE_IDENTITY() does not have these issues. That's all we use now as I've been burned once where the DBA added a trigger to a table without my knowledge... Took the longest time to figure out why that process wasn't working. Cheers, Jeff Garza ----- Original Message ----- From: "Robertson-Ravo, Neil (RX)" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Tuesday, February 01, 2005 8:20 AM Subject: RE: EASY: grabbing the id of a newly created item.. > SQL Server is simply @@IDENTITY > > > > -----Original Message----- > From: Ewok [mailto:[EMAIL PROTECTED] > Sent: 01 February 2005 15:01 > To: CF-Talk > Subject: RE: EASY: grabbing the id of a newly created item.. > > Wow, too much for so little, Just use Richard's method > > RUN YOUR INSERT QUERY > > Then directly after the insert get the records ID... > > <cfquery name="mid" datasource=""> > SELECT MAX(TheID) as LatestID FROM TABLENAME > </cfquery> > > The newest records ID from that table will now be in #mid.LatestID# > > Since you said "grab the id ( autonum, primary key)"... autonumber being > an > Access data type means the above is about your only option. Does > @@identity > even work in access? (or does anything else useful work in access for that > matter) > > There's also no need to cflock the query from above that I can think of > with > an access data source. For the most part, access locks itself when it > needs > to. > > > > -----Original Message----- > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > Sent: Monday, January 31, 2005 2:02 PM > To: CF-Talk > Subject: Re: EASY: grabbing the id of a newly created item.. > > That's a horrible way to do it. Quite inefficient. > > A better route is to check your DB's docs and see how they expose the > last inserted sequence value. In MySQL it's LAST_INSERT_ID(), with MS > SQL Server its one of three @IDENTITY variables. Run your INSERT, and > then select the value back out using this mechanism. You may or may > not need a transaction, depending on the specifics, and you definitely > won't need CFLOCK. > > cheers, > barneyb > > On Mon, 31 Jan 2005 10:24:34 -0800, Richard Colman <[EMAIL PROTECTED]> wrote: >> This is actually pretty easy in ACCESS: >> >> 1) put the enire transaction within a CFLOCK block >> >> 2) do the insert >> >> 3) then select max(id) assuming you have an autonumber field for the id >> >> Which will give you the id of the record just inserted. >> >> This is so easy that even I can do it. >> >> Rick Colman >> > > > -- > Barney Boisvert > [EMAIL PROTECTED] > 360.319.6145 > http://www.barneyb.com/ > > Got Gmail? I have 6 invites. > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:192561 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
