>A customer asked me to evaluated the following situation. They want to update parts on-hand quantities of my VFP 9 app (with SQL Server DB) from another system (based on the Oracle DB). I offered them a Web API approach but their IT said No this approach. So I am considering the following scenario:
>
>I will create an Interface table in our SQL Server DB (would it be more correctly to use the term Staging table?

For me a staging table is a table preparing datasets for next actions. A table which triggers on every insert MIGHT be called that way, but in our company.

>But the name is not that important).

True for the sound bites - but it it important to hammer out an operational definition for the term used, so that both sides are certain to agree about what was spoken. You might google on linked servers as preparation for ideas and terms.

>I will tell their IT to create a routine where whenever they receive parts (via their system), they will add a record to this Interface/Staging table (for every parts receiving transaction).
>I will create a Trigger that will fire on every Insert. And the code in the Trigger will update my application tables (according to the part # and received QTY). The Trigger code will (at the end) either delete a record or more likely (for audit trail) set a flag in this table indicating that the record has been processed.
>
>Is the above draft a practical approach? Any "holes" in this approach?
>

Rollback covered ?