For each database engine we have different ways to retrieve the last inserted id, let’s say that we have the following table:
Product(productID, name, description)
And we have the following query:
<cfquery name=”test” datasource=”my_data”>
insert into Product (name, description) values (‘someProd’, ‘description O fThe Prod’)
</cfquery>
now for each engine we can get the last ID in the following way:
MSSQL: <cfoutput>#test.IDENTITYCOL#</cfoutput>
Oracle: <cfoutput>#test.ROWID#</cfoutput>
Sybase:<cfoutput>#test.SYB_IDENTITY#</cfoutput>
Informix:<cfoutput>#test.SERIAL_COL#</cfoutput>
MySQL: <cfoutput>#test.GENERATED_KEY#</cfoutput>
I guess that this is because each engine uses different methods and the internal database variables are different from engine to engine.
We can get more information on the ColdFusion web site here.