CFQUERY Insert / Delete Issue

In Coldfusion I have an action page that completes some database actions for moving some rows of data from a temp table to a final table.

In short, the user edits a bunch of line items (rows in a table). This is done, by having CF copy all the FINAL data from a TEMP table, allowing the user to edit the TEMP data, and then the old FINAL data is deleted and the new TEMP data is written as the FINAL data.

Here is my code example:

<!--- ITEMS  COPY --->
<cfquery name="getItems" datasource="#DSN#">
SELECT TempTableItem, Col1, Col2, Col3, Col4, Col5, Col6, Col7
FROM ItemsTemp
WHERE TempTableItem = '#form.TempTableItem#'
ORDER BY TempID
</cfquery>

<!--- Begin Audit --->
<cfloop query="getItems">
<cfquery name="AuditItems" datasource="#DSN#">
INSERT INTO ItemsAudit (DocID, Col1, Col2, Col3, Col4, Col5, Col6, Col7)
VALUES ('#form.BLID#','#getItems.Col1#','#getItems.Col2#','#getItems.Col3#','#getItems.Col4#','#getItems.Col5#','#getItems.Col6#','#getItems.Col7#')
</cfquery>
</cfloop>
<!--- End Audit --->

<cfquery name="deleteOldFinalItems" datasource="#DSN#">
DELETE FROM ItemsFinal
WHERE BLID LIKE '#form.BLID#'
</cfquery>

<cfloop query="getItems">
<cfquery name="CopyItems" datasource="#DSN#">
INSERT INTO ItemsFinal (BLID, Col1, Col2, Col3, Col4, Col5, Col6, Col7)
VALUES ('#form.BLID#','#getItems.Col1#','#getItems.Col2#','#getItems.Col3#','#getItems.Col4#','#getItems.Col5#','#getItems.Col6#','#getItems.Col7#')
</cfquery>
</cfloop>

<cfquery name="deleteTempItems" datasource="#DSN#">
DELETE FROM ItemsTemp
WHERE TempTableItem LIKE '#form.TempTableItem#'
</cfquery>

-----------------
I added the  AUDIT table at some point to try and track this.  the AUDIT table does capture the new FINAL data, it just doesn't get placed in the FINAL table (the FINAL table looks empty for the new data)

To me, it is as if the DELETE Final items is executed AFTER I copy the new final items to the table thus deleting both the old and new final data. This happens very intermittently...90% of the time this code works as intended. Is it possible that due to server slowdown or some other issue, that the SQL queries could actually be executed on top of each other or backwards so that delete comes after insert instead of the way it is intended, delete then insert?

Maybe I should try moving both delete and insert into the same CFQUERY tag?
garyttuAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Since you're not using transactions, its entirely possible that other threads could be executing the code at the same time. Meaning another thread could peform a delete in the middle of your insert loop. You should always use transactions when multiple sql statements must be treated as a single unit.  

On a side note, an INSERT INTO / SELECT ..FROM statement would be more efficient than looping and inserting the records one at a time.
0
 
garyttuAuthor Commented:
Sry the BLID and DocID are the same column...I forgot to change that for the example
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.