A question came up today at work about the
<cftransaction> tag. (Yes, we still use ColdFusion. There are dozens of us. Dozens!) The question was if the tag would automatically rollback changes or if you needed to explicitly type <cftransaction action="rollback"> to rollback the changes. I have always assumed the latter.
So here is my test case. First I created 2 test tables in the database for testing each with a test record.
CREATE TABLE test_table_1 (
tt1_record_id NUMBER(3) NOT NULL,
tt1_text VARCHAR2(100),
CONSTRAINT pk_test_table_1 PRIMARY KEY (tt1_record_id)
);
CREATE TABLE test_table_2 (
tt2_record_id NUMBER(3) NOT NULL,
tt2_text VARCHAR2(100),
CONSTRAINT pk_test_table_2 PRIMARY KEY (tt2_record_id)
);
INSERT INTO test_table_1 (
tt1_record_id,
tt1_text
)
VALUES (
1,
'Blue'
);
INSERT INTO test_table_2 (
tt2_record_id,
tt2_text
)
VALUES (
1,
'Triangle'
);<cfquery name="variables.qryBefore" datasource="#application.dsn#">
SELECT
tt1_text, tt2_text
FROM
test_table_1,
test_table_2
WHERE
tt1_record_id = tt2_record_id
</cfquery>
<p>
<cfoutput>
Before:
#variables.qryBefore.tt1_text#
#variables.qryBefore.tt2_text#
</cfoutput>
</p>
<cftry>
<cftransaction>
<cfquery name="variables.qryUpdate1" datasource="#application.dsn#">
UPDATE
test_table_1
SET
tt1_text = 'Red'
WHERE
tt1_record_id = 1
</cfquery>
<cfquery name="variables.qryUpdate2" datasource="#application.dsn#">
UPDATE
test_table_2
SET
tt2_text = 'Square'
WHERE
foo = bar
</cfquery>
</cftransaction>
<cfcatch type="any">
<p>
Database update failed. Changes should have been rolled back.
</p>
</cfcatch>
</cftry>
<cfquery name="variables.qryAfter" datasource="#application.dsn#">
SELECT
tt1_text, tt2_text
FROM
test_table_1,
test_table_2
WHERE
tt1_record_id = tt2_record_id
</cfquery>
<p>
<cfoutput>
After:
#variables.qryAfter.tt1_text#
#variables.qryAfter.tt2_text#
</cfoutput>
</p><cftransaction action="rollback"> to the <cfcatch> block and gotten the same result.
So why use the rollback command then? You may only want to rollback to a certain savepoint. Another use case I have found for it is unit testing. In my test, I want to execute the entire method even the database code. However, before I leave the
<cfif this.testing> <cftransaction action="rollback" /> </cfif>
