Waiting until all the data has arrived


I have to develop a solution to solve the following problem.

We have an application which when a new record is entered, it uses three or four screens and the data is written to as many tables. We are using webMethods to interface this system to another database. The standard webMethods adapter notifications are in place which place a trigger on the table and creates a buffer table which captures the data.

However, I would like to do the following. I would like to configure the interface so that it will pick the data from the buffer tables and wait until webMethods has collected some of the fields from the other buffer tables. For example…

As it is now:
table1, cols a,b and c
table2, cols d,e and f
table3, cols h, i and j

webMethods polls the buffer for table1, and on finding something it processes another service. the same for table2 and table3.

I would like to change it so that.

table1, cols a,b and c
table2, cols d,e and f
table3, cols h, i and j

when webMethods finds something in the buffer of table1, it will wait until it the notification has retrieved data from table2 (col e) and table3 (col i and j). When it confirms that it has collected all the data required, it will then pass the documents to the next step in the process.

Any advice on the best way to achieve this would be great. I could probably configure some sort of flow service to store the data in a table or something, but it would be better if there was a way internal to webMethods.


The best way to achieve this is to not trigger until after all the data has been written to all the tables. One approach that might work is to trigger only on table3. Presumably the records in each table share some key. Using the key from table3, you’d read the data from table1 and table2.

Another approach, which I’ve used in the past, is to use a column in table1 that indicates “write” status. When the table1 row is written, the column has some status such as “start”. Then when all related records are written, a trigger (or the app itself) changes the column for the appropriate row in table1 to “done.” A trigger is configured then to fire on update when column is set to “done.”

In other words, have the app or the DB tell you when it’s done writing, don’t put that logic into Integration Server otherwise you risk getting broken when the app team decides to change the order in which they write the records.

webMethods is a company, not a product