In Praise of “update_on”
I’ve been working on an interface between two systems.
The interface has to watch system A and send transactions to system B. Normally, in the Oracle universe, there would be 2 ways to do this:
- Use database triggers on System A’s tables to trigger transactions
- Keep a separate record of the state of System A’s tables and compare it to the actual state - looking for differences which trigger transactions
The drawback with the first method is the performance hit of the database triggers on System A, as well as the possibility they might introduce errors into its processing.
The drawback with the second method is that replicating parts of the watched system is, well, just plain yucky.
Fortunately, in the case of this particular interface, System A uses a column called UPDATE_ON in its major tables. This column is updated whenever there is a change in the row. This means the interface just has to look for rows where UPDATE_ON is greater than when it last looked. It’s a much lighter touch than the other methods.
I encourage Data Architects everywhere to include an UPDATE_ON on their major tables in their designs - even if it’s usefulness isn’t immediately apparent.
