Scary concurrency problem - best practices?

[Maury Markowitz]

In order to offer true "no touch" editing on our main data table (tblOrders),
all of my editor forms are bound to temp tables (tpOrders). When the form is
opened I copy the live row out of tblOrders into tpOrders, and when they save
I copy it back. This way if _anything_ goes wrong, I don't end up copying
back bad data.

Now I'm hooking this system up to an external source of data that saves
itself into another table, tblFills. The sum of tblFills grouped by the
orderId is the total amount of the order that's been processed so far. I'm
going to copy that sum back into tblOrders with a trigger (I think).

Here's the problem: what happens when the user Saves the form and it's
copying back into the main table and the trigger fires? This is a very real
case, because the user CAN edit the data in tpFills (tblFill's temp mirror),
which would cause the trigger to fire.

Is this bad? Is there some way to avoid race conditions here? I'm thinking
that if I first copy all the fill information the original tblOrder will be
updated, and then I can copy the tpOrders info back, everything but the
fields the trigger handles. But what happens if the trigger is slow?

Or maybe I could do it the other way, copy over all the tpOrders stuff and
then copy over the fills, so the tblOrders copy is no longer busy when the
trigger fires.

Any advice here? It sounds like something that's common enough that someone
out there has worked through this before.

Maury