Notifications - Old and New Data

Gary Blomquist was kind enough to respond to my post about Data modification notifications and emailed me with an interesting document about implementing such a case using TCP/IP data transfer to all registered applications. We use a similar system with an Oracle package called dbms_alert. The paper Gary sent me proposed to send an SQL statement which would select the new data - We in turn send the table name and generate our own statement according to the table's name and additional data such as primary key values.

The problem in both methods is the same: We cannot provide the Old Data to the applications registering for modifications. This old data is sometimes crucial, and an example follows.

Suppose a stock item is referencing the shelf it is located on. Suppose the application has a view showing the sum of items on each shelf. If a stock item was to be moved from one shelf to another, the application's sum should reflect that. Changing the shelf the item was moved To is easy: It's the new shelf the item is referencing. But what shelf should have its sum reduced?

This is just a simple example, and we encounter problems like this all over our project. At the moment, we are required to re-create these sums. Since we want to send smart updates to the clients, we need some smart business logic behind it too, so that the clients won't just refresh their entire data cache.

Having an option to view at the old values would solve this instantly: I would know exactly which shelf to remove an item from.

I would like to thank Gary Blomquist again for sending me the article - It was refreshing to read of new methods, and also a great pleasure to know that in this community, people really help each other.