CFTRANSACTION not rolling back

[RayBees]

Greetings

I am performing a series of deletes from several tables and one selection. It
does catch the an error however, it commits all the deletes exception the one
that threw the error. Why?

Here is the mock up of my code:


<CFSET Commit = "Yes">
<CFSET errCount = 0>
<CFSET arrErrorMessage = ArrayNew(1)>


<CFTRANSACTION action="begin">

<!--- Get FSubFeatureID form CS_SubServices table used to detetmine if a
section has contact information --->
<CFTRY>
<CFQUERY name="GetAllSubServiceID" DATASOURCE="DSN">
SELECT
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to retrieve SubServiceID from
table1. Please contact webmaster.">
</CFCATCH>
</CFTRY>

<!---
************************************************** ******************************
******* --->
<!--- ************************** DELETE FROM ADMIN TABLES
**************************** --->
<!---
************************************************** ******************************
******* --->

<CFIF GetAllSubServiceID.recordcount GT 0>
<CFOUTPUT query="GetAllSubServiceID">
<CFTRY>
<CFQUERY name="DeleteFeatures" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table2. Please
contact webmaster.">
</CFCATCH>
</CFTRY>
</CFOUTPUT>
</CFIF>


<CFTRY>
<CFQUERY NAME="DeleteSections" DATASOURCE="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table3. Please
contact webmaster.">
</CFCATCH>
</CFTRY>



<CFTRY>
<CFQUERY name="DeleteLinks" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table4. Please
contact webmaster.">
</CFCATCH>
</CFTRY>



<CFTRY>
<CFQUERY name="DeleteService" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table5. Please
contact webmaster.">
</CFCATCH>
</CFTRY>


<!---
************************************************** ******************************
******* --->
<!--- ************************* DELETE FROM PUBLIC TABLES
**************************** --->
<!---
************************************************** ******************************
******* --->

<CFIF GetAllSubServiceID.recordcount GT 0>
<CFOUTPUT query="GetAllSubServiceID">
<CFTRY>
<CFQUERY name="DeletePROD_Features" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table5. Please
contact ">
</CFCATCH>
</CFTRY>
</CFOUTPUT>
</CFIF>



<CFTRY>
<CFQUERY NAME="DeletePROD_Sections" DATASOURCE="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table6. Please
contact webmaster.">
</CFCATCH>
</CFTRY>



<CFTRY>
<CFQUERY name="DeletePROD_Links" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table7. Please
contact webmaster.">
</CFCATCH>
</CFTRY>



<CFTRY>
<CFQUERY name="DeletePROD_Service" Datasource="DSN">
DELETE
</CFQUERY>

<CFCATCH type="database">
<CFSET Commit = "No">
<CFSET errCount = errCount + 1>
<CFSET arrErrorMessage[errCount] ="Unable to delete from table8. Please
contact webmaster.">
</CFCATCH>
</CFTRY>

</CFTRANSACTION>



<CFIF Commit EQ "Yes">
<CFTRANSACTION action="commit" />
<CFLOCATION url="index.cfm?fuseaction=ServicesGuide">
<CFELSE>
<CFTRANSACTION action="rollback" />
<CFINCLUDE template="dsp_ErrorMessage.cfm">
</CFIF>

[OldCFer]

The trouble is you are error handling all problems then continuing to do
deletes. You should
structure your cftransaction something like this. It's much simpler:

<cfset progressMsg = "">
<cftransaction action="begin">
<cftry>
Run Select query
<cfset progressmsg = "Select OK">
Run Delete 1
<cfset progressmsg = "Delete 1 OK">
Run Delete 2
...........
<cfcatch type="database">
<cftransaction action="rollback"/>
#Progressmsg#
</cfcatch>
</cftransaction>

Now the first time a DB error occurs, it jumps to the catch block and rolls
everything back.
BTW the
<CFIF Commit EQ "Yes">
<CFTRANSACTION action="commit" />
in your code doesn't do anything. SInce it follows the closing
</cftransaction> commits
or rollbacks have already been done. You can't do anything at this point.