This article was originally published on November 12, 2008.
There’s a handy little query you can run in SQL to retrieve the identity (primary key) value of a record you just created. So, you can do something like this:
INSERT INTO People ( Name ) VALUES ( 'Joe' ); SELECT @@IDENTITY AS NewName;
This query will create a new person and also return the identity value of the newly created record. It can be useful for all kinds of things – I use it frequently.
We recently upgraded to ColdFusion 8 here at Purdue, and my code started breaking.
It turns out that CF8 attempts to “help” you by automatically running
SELECT @@IDENTITY after any
INSERT query. This is problematic in two ways – it cancels out your own
@@IDENTITY query, and it arbitrarily names the result
So instead of being able to do something like this:
<cfset TheNewName = People.NewName>
You must use this:
<cfset TheNewName = People.Generated_Keys>
It’s really more annoying than anything else. It seems like a good idea to include the code automatically, but I think it would probably be a little better if there were a conditional in there that prevents CF8 from inserting its automatic query if a
SELECT @@IDENTITY query is already present.