This tag is unsupported in CFFiddle.
For enterprise database management systems that support transaction processing, instructs the database management system to treat multiple database operations as a single transaction. Provides database commit and rollback processing. See the documentation for your database management system to determine whether it supports SQL transaction processing.
action = "begin|commit|rollback|setsavepoint" isolation = "read_uncommitted|read_committed|repeatable_read" savepoint = "savepoint name"> </cftransaction>
You can specify this tag's attributes in an attributeCollection attribute whose value is a structure. Specify the structure name in the attributeCollection attribute and use the tag's attribute names as structure keys.
cfinsert, cfprocparam, cfprocresult, cfquery, cfqueryparam, cfstoredproc, cfupdate; Commits, rollbacks, and transactions and Tags as functions and operators in Developing ColdFusion Applications
ColdFusion 8: Added the setsavepoint value to the action attribute. Added the savepoint attribute.
Attribute |
Req/Opt |
Default |
Description |
---|---|---|---|
action |
Optional |
begin |
|
isolation |
Optional |
|
Isolation level, which indicates which type of read can occur during the execution of concurrent SQL transactions. The possible read actions include dirty read, in which a second SQL transaction reads a row before the first SQL transaction executes a COMMIT; non-repeatable read, in which a SQL transaction reads a row and then a second SQL transaction modifies or deletes the row and executes a COMMIT; and phantom, in which a SQL transaction reads rows that meet search criteria, a second SQL transaction then generates at least one row that meets the first transaction's search criteria, and then the first transaction repeats the search, resulting in a different result set.
|
savepoint |
Optional |
|
The name of the savepoint in the transaction. Setting savepoints lets you roll back portions of a transaction. For example, if your transaction includes an insert, an update, and a delete, and you set a savepoint after the update, you can roll back the transaction to exclude the delete. |
nested |
Optional |
true |
This attribute specifies whether the cftransaction tag can be nested inside another cftransaction tag. If the attribute value is false and there is a parent cftransaction tag, ColdFusion generates an error. |
If you do not specify a value for the action attribute, automatic transaction processing proceeds as follows:
You can now nest cftransaction tags. Typically, ColdFusion 9 does not support nested transactions, but you can embed one cftransaction tag inside another. If you nest these tags, only the outermost cftransaction tag takes effect.
This feature lets you write functions that must run in a transaction without considering whether the function is called by code that is inside a cftransaction tag. Use a cftransaction tag in the function. If the calling code is in a transaction, the tag has no effect. If the calling code is not in a transaction, the tag starts the transaction.
The following code shows nested transaction tags.
<cftransaction> <cfquery name="iquery" datasource="dsn"> insert into region(regionid, regiondescription) values('111', 'YPR') </cfquery> <cftransaction> <cfquery name="iquery" datasource="dsn"> update region set regiondescription = 'new' where regionid='111' </cfquery> </cftransaction> </cftransaction>
In a realistic situation, the second cftransaction and cfquery can be written in a CFC that are, in turn, called by the first cftransaction and cfquery by passing the regionid value.
the cfquery tag into one business event. Changes to data that is requested by the queries are not committed to the datasource until all actions within the transaction block have executed successfully. <p>This a view-only example. <!--- <cftransaction> <cfquery name='makeNewCourse' datasource='Snippets'> INSERT INTO Courses (Number, Descript) VALUES ('#myNumber#', '#myDescription#') </cfquery> <cfquery name='insertNewCourseToList' datasource='Snippets'> INSERT INTO CourseList (CorNumber, CorDesc, Dept_ID, CorName, CorLevel, LastUpdate) VALUES ('#myNumber#', '#myDescription#', '#myDepartment#', '#myDescription#', '#myCorLevel#', #Now()#) </cfquery> </cftransaction> --->
You can set savepoints at the completion of insert, update, and delete actions of a transaction. You then use error handling logic to determine whether it is necessary to roll back to a previous savepoint.
<!--- This example performs batch processing of withdrawals ---> <!--- from a bank account. The withdrawal amounts are stored ---> <!--- in an array. ---> <!--- There is a CFC named bank.cfc whose contains appear ---> <!--- after the example. ---> <cftransaction> <!--- Get the account balance. ---> <cfinvoke component="bank" method="getBalance" returnvariable="getacctbalance" accountnum=1> <cfloop index="withdrawnum" from="1" to="#ArrayLen(withdrawals)#"> <!--- Set a savepoint before making the withdrawal. ---> <cfset noxfer = "point" & #withdrawnum#> <cftransaction action = "setsavepoint" savepoint = "#noxfer#"/> <!--- Make the withdrawal. ---> <cfinvoke component="bank" method="makewithdrawal" returnvariable="getacctbalance" accountnum=1 withdrawamount="#withdrawals[withdrawnum]#"> <!--- Get the account balance. ---> <cfinvoke component="bank" method="getBalance" returnvariable="getacctbalance" accountnum=1> <!--- If the balance is negative, roll back the transaction. ---> <cfif getacctbalance.balance lt 0> <cftransaction action="rollback" savepoint="#noxfer#" /> </cfif> </cfloop> </cftransaction> <!--- The bank.cfc contains the following: cfcomponent> <cffunction name="getBalance" access="public" returntype="query"> <cfargument name="accountnum" type="numeric" required="yes"> <cfquery name="getacctbalance" datasource="testsqlserver"> SELECT * FROM dbo.mybank WHERE accountid = #accountnum# </cfquery> <cfreturn getacctbalance> </cffunction> <cffunction name="makewithdrawal" access="public" returntype="query"> <cfargument name="accountnum" type="numeric" required="yes"> <cfargument name="withdrawamount" type="numeric" required="yes"> <cfquery name="withdrawfromacct" datasource="testsqlserver"> UPDATE dbo.mybank SET balance = balance - #withdrawamount# WHERE accountid = 1 </cfquery> <cfinvoke method="getBalance" returnvariable="getacctbalance" accountnum=1> <cfreturn getacctbalance> </cffunction> </cfcomponent> --->
Sign in to your account