cftransaction

Description

Groups multiple queries into a single unit. The cftransaction tag provides commit and rollback processing. See Usage for details.

Syntax


<cftransaction 

  action = "begin" or "commit" or "rollback"

  isolation = "Read_Uncommitted" or

    "Read_Committed" or

     "Repeatable_Read" >

</cftransaction>

Attributes

Attribute

Description

action
Optional. The actions are as follows:

begin, which indicates the start of the block of code to be executed. It is the default value.

commit, which commits a pending transaction.

rollback, which rolls back a pending transaction.

isolation
Optional. ODBC lock type. Valid entries are:

Read_Uncommitted

Read_Committed

Repeatable_Read

Serializable

Attribute	Description
action	Optional. The actions are as follows: begin, which indicates the start of the block of code to be executed. It is the default value. commit, which commits a pending transaction. rollback, which rolls back a pending transaction.
isolation	Optional. ODBC lock type. Valid entries are: Read_Uncommitted Read_Committed Repeatable_Read Serializable

Usage

A transaction block is created within these tags:

<cftransaction>

  queries to be executed

</cftransaction>

Within the transaction block, you can commit a transaction by nesting the <cftransaction action = "commit"/> tag or roll the transaction back by nesting the <cftransaction action = "rollback"/> tag within the block.

Within a transaction block, you can write queries to more than one database; however, you must commit or rollback the transaction to one database before writing a query to another database.

Using CFML error handling, you control whether each transaction is committed, based on the success or failure of the database query.

Use the isolation attribute for additional control over how the database engine performs locking during the transaction.

Example

<!--- This example shows the use of cftransaction --->



<html>

<head>

<title>cftransaction Example</title>

</head>



<body>

<H3>cftransaction Example</H3>



<P>cftransaction can be used to group multiple queries

using cfquery into a single business event. Changes to data

requested by these queries can be committed on the basis 

of the success or failure of the query by using the actions 

commit and rollback, respectively.

<P>The following is a sample listing (see code in right pane):

<!---------------------------------------------------------------------

  You can enclose the transaction in a cftry/cfcatch block in order

  to catch database errors and make commitment or rollback or the 

  transaction contingent on the errors received.

---------------------------------------------------------------------->

<cftry>

<----------------------------------------------------------------------

Initialize commitIt to Yes.

---------------------------------------------------------------------->

<cfset commitIt = "Yes">

<cftransaction action = "begin">

  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>

  INSERT INTO Courses

    (Course_Num, Descript)

  VALUES

    ('#myNumber#', '#myDescription#')

  </cfquery>



  <!------------------------------------------------------------------

  Rolls back the pending insertion if database exception is caught.

  ------------------------------------------------------------------->

  <cfcatch type = "DATABASE">

    <cftransaction action = "rollback"/>

    <cfset commitIt = "No">

  </cfcatch>

  <cfif commitIt>

    <cftransaction action = "commit"/>

    <!--------------------------------------------------------------

    Commits the pending insertion.

    ---------------------------------------------------------------->

  <cfelse>

    <cfset commitIt = "Yes">

  </cfif>











  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>

  INSERT INTO CourseList

    (CorNumber, CorDesc, Dept_ID,

    CorName, CorLevel, LastUpdate)

  VALUES

    ('#myNumber#', '#myDescription#', '#myDepartment#',

    '#myDescription#', '#myCorLevel#', #Now()#)

  </cfquery>

  <!------------------------------------------------------------------

  Rolls back the pending insertion if database exception is caught.

  ------------------------------------------------------------------->

  <cfcatch type = "DATABASE">

    <cftransaction action = "rollback"/>

    <cfset commitIt = "No">

  </cfcatch>

  <cfif commitIt>

    <cftransaction action = "commit"/>

    <!--------------------------------------------------------------

    Commits the pending insertion.

    ---------------------------------------------------------------->

  <cfelse>

    <cfset commitIt = "Yes">

  </cfif>

</cftransaction>

</cftry>



</body>

</html>