ColdFusion 8 and MSSQL triggers

Coldfusion 8 handles MSSQL triggers different that older versions of Coldfusion.

IN older CFMX

We had some For insert triggers on tables for updating related tables etc.. The last trigger used to return the new identity using select SCOPE_IDENTITY() as @iNewRec

the cfquery was

<cfquery name="qInsert" datasource="#myDSN#">
insert into tbl1 (name,fname)
values
('test1','test2')
</cfquery>


Then you could do the

<cfset variables.iNewId = qInsert.iNewRec />

In CFML 8 this will not work. You will get the error that qInsert is Undefined.

The solution is
a) make sure that the select SCOPE_IDENTITY() as @iNewRec is executed before any update/insert delete statement in any trigger in the sequence...

or

b) In the cfquery tag after the insert add a dummy select

<cfquery name="qInsert" datasource="#myDSN#"> insert into tbl1 (name,fname) values ('test1','test2') select 'ttt' as t </cfquery>