Hi,
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? But the name is not that important).
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?
TIA
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham