You won't be able to do this directly from CF with anything built into CF. This kind of thing is called a distributed transaction.
You might see if you can do this from within one of the databases itself, if the databases can potentially talk to each other. Otherwise, you'll basically have to maintain the state somehow in your application until all transactions have been completed. For example, you could copy the original records into a separate table in the existing database before changing them, then copy them back if you need to roll back.
Dave Watts, CTO, Fig Leaf Software
Edit your insert query to exclude records it already has.
Because I am migrating one or more records at a time what I did was create a mirror table inside the firewall. In that table I store the PK value (along with all of the other data) of the record being migrated. During the migration process I generate a list of PK’s I am migrating from the DMZ through the firewall. I use that list to run a query against the mirror table. If any matches are found I stick them into a structure. Then as I loop through my data processing routines I check to see if the PK value of the record being processed is in the structure or not. If it is I don’t process the record but still set the flag as processed later on. It works but it is as cheesy as it gets!!!
Have an Ordinary Day...