cftrasnactipn with cftry/cfcatch

[GUJUm0deL]

Hi folks, can you all look this code over and let me know if its valid?

<cftransaction>
	<cftry>
		<cfquery name="deletefirst" datasource="#DB#">
			DELETE  test2.dbo.myTable 
			FROM	test2.dbo.myTable a inner join test1.dbo.myTable b ON a.myTable_ID = b.myTable_ID
			WHERE	b.is_Live = 1		
		</cfquery>
		
		<cfquery name="my_DTS" datasource="#DB#">exec master..xp_cmdshell 'dtsrun.exe /S abcde /E /N my_DTS'</cfquery>
	<cfcatch type="Database">
		<cfmail from="" to="" subject="Error pushing DTS package for <cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" type="html">
			SOME TEXT
		</cfmail>
		
		<cflocation url="somelink.cfm" addtoken="no">
	</cfcatch>
	</cftry>
<cftransaction>

My concern is the <cftransaction> tags. I tried to make the DTS package fail on purpose (I just changed the DTS package name to one that doesn't exist), but the DELETE statement did not rollback.

I read on some sites that using <cftransaction> with <cftry>,<cfcatch> can be tricky.

Any suggestions?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[imstillatwork]

For starters you need to use the "action=" attribute
values are BEGIN, ROLLBACK, and COMMIT

check the cfml reference for your version, there where some slight changes in the way it is used.

Kevin

http://www.lot-o-nothin.com/cfml/

 

[GUJUm0deL]

I thought by default <cftransaction> will rollback all queries if one of them fails.

Would a <cftransaction> still work if one were using it within a query AND a DTS package call?? I've never seen it used that way.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[imstillatwork]

not sure....

Kevin

http://www.lot-o-nothin.com/cfml/

 

[Sarky78]

this is what i tend to do:

<cfset error =0>
<cftransaction action="begin">
<cftry>

<cfquery name="".....</cfquery>

<cfcatch type="Database">
<cfset error = 1>
</cfcatch>

</cftry>

<cfif NOT error>
<cftransaction action="commit"/>
<cfelse>
<cftransaction action="rollback"/>
</cfif>
</cftransaction>

the ordering might be the wrong way around, the commit/rollback might need to be outside the first cftransaction block but that should be it.

hope this helps!

Tony