CFINSERT /w CFTRANSACTION question...

[plook]

Hi everybody !

I would like to ask your opinion !

I am posting a record in an SQL db (MS SQL 7) using CFINSERT. Which works fine. When posting the record, the SQL Server generates an ID to identify that record. I would like to return that ID immediately after the record is posted to use it in another CFINSERT. Since I don't think CFINSERT itself could return something I have a solution and would like to ask you guys what you think about it... First I open a CFTRANSACTION, then do the first CFINSERT,then send a query to find the biggest ID number,do the second CFINSERT using that ID returned from the query...then close the CFTRANSACTION...

so..
<CFTRANSACTION>
<CFINSERT #1>........
<CFQUERY ...>.....
<CFINSERT #2>.....
</CFTRANSACTION>

My concern is what would happen if 2,3 or 10 people post something using that Form at the same time? Is it dangerous that the CFQUERY between the 2 CFINSERTs return an ID posted by somebody else or CFTRANSACTION is supposed to &quot;lock&quot; the access until the completion of that transaction ?

Thanks for your feedback...

P.S. If somebody knows a better way to return that ID number it would be very appreciated to know it )

Thanks

Dominic

 

[ded]

You could always take the data which you use in the CFINSERT to make sure you retrieve the correct ID number

For example if the data you are posting is

FORM.firstname, FORM.lastname and FORM.email

then the chance of two people posting the same details at the same time is lower. Therefore if you change your query to

<CFQUERY NAME=&quot;x&quot; DATASOURCE=&quot;y&quot; MAXROWS=&quot;1&quot;>
SELECT ID
FROM table
WHERE firstname = #FORM.firstname#
AND lastname = #FORM.lastname#
AND email = #FORM.email#
ORDER BY ID DESC
</CFQUERY>

Okay this will still return more than 1 record but it is safer than just finding the biggest ID. Another method to prevent two users posting at the same time would be to use the <CFLOCK> tag.

Regards,
Duncan

 

[Guest_imported]

The dbase does store the latest &quot;ID&quot; number in an identity feild....

<cfquery datasource=&quot;#DSN#&quot;>
Insert into events(EventName)
Values('testing')
</cfquery>


<cfquery datasource=&quot;#DSN#&quot; name=&quot;test&quot;>
select @@identity as ID_num
</cfquery>


<cfoutput query=&quot;test&quot;>
#ID_num#
</cfoutput>