CODEWITHSUNDEEP
t-sqlcoldfusioncfmlcfquerycoldfusion-2018
James A Mohler
James A Mohler

Reputation: 11120

Removing Transactions from cfquery s

I had some original code which looked like

<cftransaction>
   <cfquery>
   UPDATE
   ...
   </cfquery>
   <cfquery>
   UPDATE
   ...
   </cfquery>
   <cfquery>
   DELETE
   ...
   </cfquery>
</cftransaction>

I am looking to change the code to

<cftransaction>
<cfscript>
QueryExecute("
 UPDATE
 ...

 UPDATE
 ...

 DELETE
 ... 
 ");

</cfscript>
<cftransaction>

Do I still need to use <cftransaction> ?

Upvotes: 2

Views: 166

Answers (1)

SOS
SOS

Reputation: 6550

Yes.

SQL Server defaults to auto-commit mode. Without the explicit transaction, each individual statement would be committed after it executes. Just as they would if you executed the SQL string in SSMS.

Here's an example to demonstrate. It executes three UPDATE statements in a row (deliberately causing an error on the second one). Without a transaction, the first and last UPDATE succeed, but the middle one is rolled back. So if all three statements should be handled as a single unit, you must use a cftransaction.

Before:

Image of data before queries

After:

Image of data after queries, without a transaction

DDL/Sample Data:

CREATE TABLE SomeTable ( Id INT, Col VARCHAR(50) )

INSERT INTO SomeTable
VALUES (1,'Original'),(2,'Original'),(3,'Original')

CF

<cfscript>
   before = QueryExecute(" SELECT * FROM SomeTable");
   writeDump( before );

   // DEMO: Deliberately omits cftransaction and causes error
   try {
      QueryExecute(" UPDATE SomeTable SET Col = 'Changed' WHERE Id = 1
                     UPDATE SomeTable SET Col = 'Changed' WHERE Id = 2 AND Id = 1 / 0
                     UPDATE SomeTable SET Col = 'Changed' WHERE Id = 3
                 ");
   }
   catch( any e) {
      writeDump( e );
   }

   after = QueryExecute(" SELECT * FROM SomeTable");
   writeDump( after );
</cfscript>

Upvotes: 4

Related Questions