I need to catch changes to an Oracle database. Changes in one table are fine, you use a notifier adapter. But if you need to get more data from the database than just from the one table…
When the Address table is updated, I need to retrieve some corresponding information from the Person table before publishing the change document. As far as I can see, there are 3 options:
Place notifiers on the Address table. Subscribe to the document this produces. In the invoked service, utilize select adapters to get the additional information needed before publishing the “final” document.
Place notifiers on the Address Table. Subscribe to the document this produces. In the invoked service, call a stored procedure in the database which returns the information I want and publish.
Create a new table in the database containing fields for the data that need to be published. Create a stored procedure that is triggered by changes to the Address table, collects the needed information and puts it in this table for a short duration. Place insert notifier on this table and subscribe to this “final” document.
I can see a possible performance issue with the “ping-pong” traffic between WebMethods and Oracle in solution 1, and solution 3 creates two “temporary” tables where one is sufficient, strictly speaking.
Does anyone have views on what would be the best practice here, or a different and better solution?