Thanh, If you use a trigger that inserts new records into associated tables, the @@IDENTITY won't work. Those new records defined in the trigger will change the @@IDENTITY to an incorrect value.
t ********************************************************************** Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. 615 SW Broadway Ste. 200 Portland, OR 97205 503.221.9860 ext. 111 http://isitedesign.com ********************************************************************** -----Original Message----- From: Thanh Nguyen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 20, 2002 3:27 PM To: CF-Talk Subject: RE: Returning IDs off a query... What if I have an Insert trigger, will the SELECT @@IDENTITY work? Are there any problems with using this with triggers? -----Original Message----- From: Tyler M. Fitch [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 20, 2001 1:41 PM To: CF-Talk Subject: RE: Returning IDs off a query... If you have no triggers being fired by your table on inserts then the SELECT @@IDENTITY will work just fine. You don't have to wrap it in <cftransaction> either. Once a CFQUERY is executed in a page the connection is left open for more DB actions until the page is done processing, since it's a single connection, that is what allows for the use of @@IDENTITY. If your DBMS supports it I'd use it instead of a SELECT MAX(). Cheers, t ********************************************************************** Tyler M. Fitch Certified Advanced ColdFusion 5 Developer ISITE Design, Inc. 615 SW Broadway Ste. 200 Portland, OR 97205 503.221.9860 ext. 111 http://isitedesign.com ********************************************************************** -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 20, 2001 1:27 PM To: CF-Talk Subject: Re: Returning IDs off a query... I always use SELECT MAX(id) FROM tbl_name after the insert. I do wrap it in cftransaction though just in case. That way, I can be confident that the last entry was mine and not someone else that submitted at the same time. HTH Michael Corrigan Programmer Endora Digital Solutions www.endoradigital.com 630/942-5211 x-134 ----- Original Message ----- From: Tyler Silcox To: CF-Talk Sent: Thursday, December 20, 2001 2:30 PM Subject: Returning IDs off a query... I've been saving this question for a rainy day, and while it's not rainy, it could be getting a little stormy...what is the best way to return a freshly submitted ID from a cfquery? Should I use a getMax SQL function with cftransaction tag pair around the insert and select queries or should I retrieve the @@identity in a separate cfquery statement? Any takers? Tyler ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
