Using the CFTRANSACTION tag to Roll Back SQL Changes in The Event of an Error

If you have ever had to run a series of SQL statements that alter the table or tables, then you know the fear of encountering an error in the middle of the script!  Let’s say you need to run a series of Insert statements and it errors out before it finishes.  You now have extraneous rows in your table or tables that immediately affect your data integrity.  So, how do we prevent this?  Well, the CFTRANSACTION tag saves the day by allowing you to store all of the SQL changes in an array and not actually committing them to the DB until the script completes error free!  We use the CFTRANSACTION tag in conjunction with CFTRY and CFCATCH.  The basic syntax is this:

<!---First we lock the ColdFusion Server to prevent any other scripts from messing us up--->
<cflock scope="server" type="exclusive" timeout="5">
    <!---now we open the cftry--->    
    <cftry>
        <!---here's where we start the cftransaction--->
        <cftransaction action="begin" isolation="read_uncommitted">
            <!---ALL OF OUR QUERIES GO HERE--->
            <cfquery name="insert1" datasource="#application.datasource#">
                INSERT INTO
                    tableName (
                        firstName,
                        lastName
                    )VALUES(
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstName#">,
                        <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastName#">
                    )
            </cfquery>
            <!---rest of inserts would go here...--->
        </cftransaction>
        <!---open a catch--->
        <cfcatch>
            <!---this cftransaction will rollback any sql changes if the script errors--->
            <cftransaction action="rollback" />
            <cfrethrow>
        </cfcatch>
    </cftry>

    <cftransaction action="commit">
</cflock>

Let me know in the comments if you have any questions!



New From: $29.00 USD In Stock
Used from: $38.87 USD In Stock



List Price: $79.99 USD
New From: $13.40 USD In Stock
Used from: $18.33 USD In Stock

  • Keith

    I have a very complex query that is likely to need extensive debugging before I get it right.  This is exactly what I needed to make sure I don’t corrupt my data!

  • Anonymous

    Glad you found it helpful!