Hi Tom,
I seem to recall that someone posted to this list a few weeks ago saying
that there were no problems at all with using the @@IDENTITY method of
getting the Identity Column. I've certainly used it ever since I've been
writing stored procedures and have never experienced any problems, even in
very high traffic sites.
Personally, I prefer the @@IDENTITY method because it's tidier than creating
triggers as well as the stored procedures. However it's great that people
bring so many other options to the list.
> From: Tom Nunamaker <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Fri, 30 Jun 2000 23:09:24 +0200
> To: [EMAIL PROTECTED]
> Subject: RE: return value of Identity Column
>
> There's another way with SQL Server using a trigger:
>
> CREATE TRIGGER GetPK_MyTable ON MyTable
> FOR INSERT
> AS
> SELECT MyTable_ID FROM INSERTED
>
> When you do your CFQUERY and perform the SQL INSERT,
> SQL server will return the inserted ID via the trigger!!!
>
> Using the @@IDENTITY variable isn't guaranteed to be
> correct under heavy load from my understanding.
>
> Tom Nunamaker
> [EMAIL PROTECTED]
>
>
> At 6/30/00 12:41 PM, you wrote:
>> There are a couple of ways, some more preferred than others.
>>
>> Option 1 (Best for Beginners and Learning, can sometimes give erroneous
>> results)
>>
>> Perform the insert into the database. My assumption is that you are using an
>> Identity field, like the Autonumber field in Access or Identity in MS SQL
>> Server. This gives every record a UNIQUE ID.
>>
>> Now immediately run a query that gets the MAX (a SQL Aggregate Function) of
>> the Identity field.
>> You should enclose this in a simple CFTRANSACTION tag.
>>
>> So,
>> <cftransaction>
>> <cfquery name="insert">
>> INSERT INTO table (field)
>> VALUES (form.values)
>> </cfquery>
>>
>> <cfquery name="get_new_id">
>> SELECT MAX(ID) as MaxID
>> FROM table
>> </cfquery>
>> </cftransaction>
>>
>> <cfoutput>
>> #get_new_id.MaxID#
>> </cfoutput>
>>
>> That will do it for the easier way and is good for learning.
>>
>> Option 2 (More inline with larger applications and advance SQL Server - uses
>> stored procedure. You'll need to read the docs on this one for additional
>> details that I will not detail here. I have seen no books with good examples
>> (anyone?). (This example is using MS SQL Server)
>>
>> Here is the Stored Procedure:*******************************
>>
>> CREATE PROC sp_insertApplicant
>>
>> @FirstName varchar(50),
>> @MiddleName varchar(50),
>> @LastName varchar(50)
>>
>> AS
>>
>> INSERT INTO tblApplicants
>> (FirstName,
>> MiddleName,
>> LastName)
>> VALUES
>> (@FirstName,
>> @MiddleName,
>> @LastName)
>>
>> SELECT ApplicantID = @@IDENTITY
>>
>> Here is the code in the CFM:************************************
>>
>> <cfstoredproc datasource="#application.dsn#" procedure="sp_insertApplicant"
>> returncode="Yes" debug>
>> <!--- CFPROCRESULT tag. This get's the results and returns them
>> to you for
>> processing --->
>> <cfprocresult name = rs1>
>>
>> <!--- CFPROCPARAM tags. This does the work of sticking the data
>> into the
>> table. --->
>> <cfprocparam
>> type="IN"
>> cfsqltype=cf_sql_varchar
>> value="#form.FirstName#"
>> dbvarname=@firstname>
>>
>> <cfprocparam
>> type="IN"
>> cfsqltype=cf_sql_varchar
>> value="#form.MiddleName#"
>> dbvarname=@middlename>
>>
>> <cfprocparam
>> type="IN"
>> cfsqltype=cf_sql_varchar
>> value="#form.LastName#"
>> dbvarname=@lastname>
>>
>> </cfstoredproc>
>>
>> Now you get a result set in the form of a variable called RS1. So, in this
>> instance we returned the new applicantID, or RS1.ApplicantID. You can use
>> RS1.RecordCount to see how many records were returned (one in this case) and
>> RS1.ColumnList for a list of Columns returned (again, one, ApplicantID).
>>
>> We can use the result, the RS1.ApplicantID, to retrieve the record we just
>> created.
>>
>> <cfquery name="get_new_record">
>> SELECT *
>> FROM tblApplicants
>> WHERE ApplicantID = #RS1.ApplicantID#
>> </cfquery>
>>
>> If I missed anything or am in error, it was the Martians who made me do it.
>> I waive all responsibility for my actions (seems to be going around these
>> days).
>>
>> This is my favorite and seems pretty fast. Feel free to contact me off list.
>>
>>
>> Gary McNeel, Jr.
>> Project Manager - DAC-Net, Research & Graduate Studies
>> Rice University - Houston
>> [Lovett Hall] 713-348-6266 (Primary)
>> [DAC] 713-348-5184
>> [M] 713-962-0885
>> [H] 713-723-9240
>>
>> "The politicians don't just want your money. They want your soul. They want
>> you to be worn down by taxes until you are dependent and helpless. When you
>> subsidize poverty and failure, you get more of both."
>> -James Dale Davidson
>>
>>> -----Original Message-----
>>> From: Shawn Mirza [mailto:[EMAIL PROTECTED]]
>>> Sent: Friday, June 30, 2000 11:59 AM
>>> To: [EMAIL PROTECTED]
>>> Subject: RE: return value of Identity Column
>>>
>>>
>>> I know its an @@id when looking it up in a stored proc
>>>
>>> -----Original Message-----
>>> From: Jason Zimmerman [mailto:[EMAIL PROTECTED]]
>>> Sent: Friday, June 30, 2000 12:35 PM
>>> To: Cf-Talk (E-mail)
>>> Subject: return value of Identity Column
>>>
>>>
>>> Is there a way to return the value of an Identity Column after an insert
>>> into a SQL db?
>>>
>>> Thanks,
>>> -Jason
>>> ------------------------------------------------------------------
>>> ----------
>>> --
>>> Archives: http://www.eGroups.com/list/cf-talk
>>> To Unsubscribe visit
>>> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>>> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>>> the body.
>>>
>>> ------------------------------------------------------------------
>>> ------------
>>> Archives: http://www.eGroups.com/list/cf-talk
>>> To Unsubscribe visit
>> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>> the body.
>>
>>
----------------------------------------------------------------------------
->> -
>> Archives: http://www.eGroups.com/list/cf-talk
>> To Unsubscribe visit
>> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
>> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
>> the body.
>
>
> ------------------------------------------------------------------------------
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in the
> body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
