I have a table that I am synchronizing with simple insert/update/delete notifications. The problem I am running into is that when a user updates a key field, the predicate on the target database falls out (doesn’t find the row to update) since there is no match on the key field (post update).
Can I access the pre-update field somehow? If I wrote the trigger myself, I could add additional columns and populate them with old.column_name and new.column_name, but since WM re-creates the trigger every time you disable and re-enable the trigger, I’m not finding an easy way to do it.
What you might try doing here is write a trigger yourself and allow it to populate the old.column_name and new.column_name in a TEMP table in your database. Then use WM Adapter’s INSERTNotification for this table to get your data. You can store additional columns apart from these two to make your work a lot more easier.
Only catch is that this is a two step process and thus gets a beating on performance, but it will work for sure.
OK, I had actually thought of that and did not want to beat up performance, so was looking for a way around it. However it appears that WM has actually thought of this and here is how to do it:
Create your own Trigger, Sequence, and Buffer Table, similar to what gets created by a regular Update Notification. Include both the OLD and NEW key fields in the buffer and trigger.
Create a BASIC notification on your buffer table, and make sure that the delete rows box is checked. Create the rest of your flow, publish, subscribe as you normally would. In the Subscribe Adapter use the OLD key columns in the Where Tab and the NEW key and dimension columns in the Update Tab.
in the notification component, webMethods would store the Trigger code. You can update the trigger code using old event type editor and save it. this will ensure that your trigger gets created everytime.
