Savepoint in cftransaction

Coldfusion 8 has added a new attribute that can be used for setting save points for your transactions.Savepoint attribute of cftransaction tag will let us to rollback some portions of transaction. It allows you to backup your transaction to any particular intermediate point.

I’ll show you how to use this attribute.
Lets have one query which has select and update operation.

[code:cf]

<cftransaction>
<!— Get data from table one —>
<cfquery name="getTblOne" datasource="testsqlserver">
SELECT * FROM dbo.table_one
WHERE Id = #tableId#
</cfquery>

<!— This statement can’t be rollback as it’s not included for save point —>
<cfquery name="updateTblOne" datasource="testsqlserver">
UPDATE dbo.table_one SET name = ‘#name#’
WHERE id = 1
</cfquery>

<!— Set a savepoint before the point to be rollback —>
<cfset point_rollback = 10>
<cftransaction action="setsavepoint" savepoint="#point_rollback#"/>

<!— Update table one which is under save point —>
<cfquery name="updateTblOne" datasource="testsqlserver">
UPDATE dbo.table_one SET name = ‘#name#’
WHERE id = 1
</cfquery>

<cfquery name="getTblOne" datasource="testsqlserver">
SELECT name FROM dbo.table_one
WHERE Id = #tableId#
</cfquery>

<!— If update fails or conditions below is true , roll back the transaction. —>
<cfif getTblOne.name eq ‘testsavePoint’>
<cftransaction action="rollback" savepoint="#point_rollback#" />
</cfif>
</cftransaction>

[/code]