I guess I'm just surprised because I'd not heard about it. Maybe it's not news to others.
I just did this and it worked (in CF5 against an Access 2000 database):
<cftransaction>And it worked. Very interesting and worth exploring further.
<cfquery datasource="dsn" name="addkey">
insert into tablename (columnname) values ('value')
</cfquery>
<cfquery datasource="dsn" name="getkey">
select @@identity as key
</cfquery>
</cftransaction>
<cfoutput>#getkey.key#</cfoutput>
One of the oldest technotes in the Macromedia (then Allaire) knowledge base, "ColdFusion Server (All Versions): Getting Auto-Increment Primary Key Values(Article 156)" from 1996 showed the approach of doing the insert and a select max(pkeyname) within a CFTRANSACTION.
Well, now you can do the same cftransaction and insert but instead do a SELECT @@IDENTITY. Why is this worthwhile? Well, one of my long held beefs with the old approach is that the CFTRANSACTION tag in and of itself doesn't guarantee that 2 templates executing the same code at the same time wouldn't get each other's pkey values. It would be critical to use the right ISOLATION attribute on the CFTRANSACTION and have that be supported by the DB to protect against two inserts at once, and since most examples leave it off, that means you'd get the default isolationlevel for your database, which varies by DBMS (and by optional configuration of a specific database). So you couldn't know FOR SURE that you were getting the right pkey, even with CFTRANSACTION.
Maybe that's only a concern in high volume environments, but it's still a worthwhile concern, especially if you've not focused on the isolationlevel to know things worked as expected. (The MM technote was updated in 2001 to add a CFLOCK around the insert and SELECT, but that only would prevent concurrent execution of that CF code--others running other CF code not in the same lock or outside of CF entirely could certainly do an insert at the same time.)
But the SELECT @@IDENTITY would appear to be much more appropriate. According to the technote, it guarantees not to get the value of another inserted record.
Now, it's not entirely clear how much more reliable this is in the CF environment. For one thing, we can't issue the SELECT in the same CFQUERY (or "batch") as the INSERT as is done in some of their examples. So whether the CFTRANSACTION really performs the same role as the batch in those examples and guarantees it's always right is yet to be proven.
Also, since the "client" in this scenario is CF and CF shares and reuses threads, it seems possible that the DBMS (the Jet engine) might be fooled into thinking more than one user is coming in on the same "thread". I'll rely on others with deeper experience or access to CF code to look into that. Frankly, one could argue that that issue is no more threatening to this approach than to the old one.
There is one potential difference, but it really doesn't apply to Access. If you were to use this approach against a SQL server table that had triggers causing inserts into other records, the SELECT @@IDENTITY would return the value of that other table's pkey. Again, this isn't unique to this opportunity to do the statement from within CF. The same issue would apply if you did the same thing in an SP in SQL Server. Just something to be aware of. It's discussed in the KB.
Indeed, I recommend anyone interested in this matter check out that MS KB. And if you learn anything more about things, please do pass along your findings and I'll post them (with accreditation) here.
Oh, and to be technically correct, this is a change in the underlying ADO mechanisms that CFQUERY handles for us, so as I implied above it's not just a change for Access but for any Jet 4 database (like Access) as well as SQL Server 6.5+. I suppose it may require a certain level of MDAC support, but I have a pretty vanilla machine (Windows 2000 pro, CF5, Access 2000 and standard installs of stuff) and it worked for me. Again, if anyone notices other details, please share them.
