Anticlue

Scope_Identity, Ident_Current, & @@Identity

Working with a DB via CF is an easy thing. Primary Key assignment is an issue that goes to the heart of the interaction.

Alot of people know about Using @@Identity to return the key, however in SQL 2000 there are new functions in town to make life alot easier.

Sometimes, one can get unexpected results using @@Identity. The reason for this is that @@Identity returns the last identity value inserted. A clearer picture maybe found with Scope_Identity which returns the last identity value inserted in the current session and the current scope. Finally Ident_Current('tablename') yields the last identity value generated for a specific table in any session and any scope.

So lets go with this scenario, say you have an insert into tblEvent that cause a trigger to fire. This trigger as an identity insert on another tblEventHistory. So in CF you do the following query.

<cfquery name="qEventInsert" datasource="#Application.cn#">
Set nocount on;

insert into tblEvent ( EventDesc ,StartDate ,EndDate, Owner )
values ( <cfqueryparam value="#Trim(variables.EventDesc)#" cfsqltype="cf_sql_varchar">,
<cfqueryparam value="#CreateODBCDate(variables.StartDate)#" cfsqltype="cf_sql_date">,
<cfqueryparam value="#CreateODBCDate(variables.EndDate)#" cfsqltype="cf_sql_date"> ,
<cfqueryparam value="#Trim(variables.UserName#" cfsqltype="cf_sql_varchar">)

select scope_identity() as EventID;
select @@identity as EventHistoryID;
Select Ident_Current('tblEvent') as LastInsertedEventID;

set nocount off;
</cfquery>

@@Identity would return the trigger value in tblEventHistory

scope_identity returns the pk value in tblEvent for this event.

Ident_Current is the last identity value of the tblEvent for all sessions of the application.