This is a good article summarizing the topic of having databases notify outside clients when changes have occurred:
Highlights from the article:
Most popular RDBMS offerings already provide for update triggers that execute commands when changes occur. If your application needs to be aware of specific changes, then you can add a trigger to the RDBMS. The downside of this approach is that many triggers must be generated to deal with very similar concerns (and improperly written triggers can severely impact DB performance).
RDBMS authors should build on the trigger technology to implement publish and subscribe change notification services. Applications (and object caches) would subscribe to change notifications by issuing statements very similar to update triggers with the addition of a callback. When the trigger is fired, the callback is invoked.
The advantage to this approach is that it cannot be bypassed. A change notification service that is integral to the RDBMS will catch any changes to the data (including those caused by stored procedures). Applications will still have to deal with the changes, but at least they will know about them.
The APIs for the RDBMS Change Notification Services should be standardized, and the callback mechanisms should be flexible to support multiple languages and protocols (like XML over HTTP). I'm probably naive, but like SQL itself, agreement on a common standard will benefit all RDBMS vendors. There should be little incentive to implement proprietary APIs.
Update: Thanks to a reader for a link to Oracle Streams. If Oracle integrates this functionality with Toplink, then Java evelopers will have something very close to what I envision. Oracle's efforts are a very good start, but we need standard APIs supported by many RDBMS offerings.
Update (20May2005): Looks like Microsoft's SQL server does pretty much what I want. Check out the devx article: Letting Java in on SQL Server Notifications. Unfortunately, there's still not really a standard. ...
Active Databases Look into the area of Active Database Management Systems and you will be surprised how much work has been done in the 90s:
An active database is a database with the event monitoring scheme for detecting when certain data is INSERTED, DELETED, UPDATED, or SELECTED, and automatically executing the actions in response when certain events happen and particular conditions are met.
The type of triggers useful for monitoring database changes in Postgres are row-level insert, update, and delete statements that occur on a specific table.
On the other hand, Postgres Rules can be used with Views.
The query rewriter is discussed in some detail in Chapter 36, so there is no need to cover it here. We will only point out that both the input and the output of the rewriter are query trees, that is, there is no change in the representation or level of semantic detail in the trees. Rewriting can be thought of as a form of macro expansion.
Page 44 of the book 'Active Database Systems By Jennifer Widom, Stefano Ceri'
POSTGRES is a next generation extensible database management system. Among the main design goals of POSTGRES was to support active rules. The first implementation of an integrated rule system is described in [SHP88]. Since then, based on the initial experience with rules and user feedback [SHP89], a second version of the POSTGRES rule system was developed, sometimes referred to as PRS2. This chapter describes the PRS2 version of POSTGRES rules. There are two alternate implementations of POSGTRES rules. The first implementation, the Tuple Level System (TLS), processes rules on a tuple by tuple basis. This system uses special markers called rule locks. When a rule is defined, those locks are stored in tuples that satisfy the rule's qualification (i.e., its condition part). When an appropriate event (e.g., retrieval of an attribute value, tuple modification, etc.) occurs in a tuple that has the appropriate rule locks, the corresponding rule actions are executed. The second implementation, the Query Rewrite System (QRS), is based on a query rewrite mechanism similar to the view implementation mechanism presented in [Sto75]. Using this approach, a query is combined with all relevant rules, and a set of modified queries is generated. The execution of this new set of queries provides the desired results.
More info on the original rules system implemented in Postgresql that was ripped out for the Postgres 95 release:
More info on the current rules system (Query Rewrite System) implemented in Postgres.
Notification on database change? Is it possible to have SQL server notify an application upon a change in a table?
So, as you can see, a Notification to Pull model is very powerful, but quite complex. There are a lot of moving parts to build, configure, maintain. We have a number of the parts including:
* SQL Server - Ability to store all this data on the server * SQL Server Compact - Ability to store all this data easily on the client with minimal deployment issues * Sync Services for ADO.NET - The pull operation to send/receive net changes between the server and the client * SQL Server Service Broker - Ability to queue up operational changes within the data center * WCF - communication stack between the client and the server over various protocols * Query Notifications - Ability for the server to notify a listener service that something has changed * Change Notifications - SQL Server 2008 introduces a more efficient way to track changes that can be used with Query Notifications to provide an efficient way to know when something changed, and what the details of the change are.
Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.
13 Developing Applications with Database Change Notification
Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits ...
Oracle has two seemingly competing technologies. CDC and DCN. What are the strengths of each? When would you use one and not the other?
November 22nd, 2004 by gregr
I while back, I wrote an application to generate RSS feeds from Visual SourceSafe databases. The code was set to expire a few months later, as we were considering building this capability into a product. A couple more times after that, I posted updated versions with a later and later expiration.
Well, I’m happy to announce that we’ve decided to release the source code for this application. It requires .NET 1.1, and build files are included for Visual Studio .NET 2003.
This really is a cool app…it runs as a Windows service, and generates RSS feeds based on the change logs in VSS. And now it’s free, unlimited, and ready for you to play with. The copyright and license are included in the readme.rtf in the package below - basically, it’s an unrestricted modification and redistribution license.
download source: VssRssSvc.zip
International Workshop on Rules in Database Systems (RIDS) - 1993, 1995, 1997
Active Rules in Database Systems By Norman W. Paton Contributor Norman W. Paton Edition: illustrated Published by Springer, 1999 ISBN 0387985298, 9780387985299 439 pages
Active Database Systems: Triggers and Rules for Advanced Database Processing By Jennifer Widom, Stefano Ceri Published by Morgan Kaufmann, 1996 ISBN 1558603042, 9781558603042 332 pages
A Survey of Active Database Systems - 1997 by Theodore Hong
Survey of Active Database Systems - 2002 Power Point Presentation by Roberta Cochrane
-- ScottLangley - 04 Mar 2009