Is it possible to listen to relational database update?

Question

Is it possible to listen to relation database update? For example, my web app want to send data update to client through Comet technology. I can have the program to poll the database periodically, but that would not be performant and scalable.

If app can hood to a "event handler" of database, then app can get notification every time given database table data is updated. This sounds more promising, but I didn't find any concrete example for it. This is listener pattern.

Does common relational database support such feature?

Answer 1

MS SQL Server has had query change notifications since 2005. Here's more information on how to use the feature: http://msdn.microsoft.com/en-us/library/ms175110.aspx

Oracle has had this since version 10g, I think. Here's more information: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm

I don't know of any other common RDBMSes that support this.

Answer 2

It is definitely non-standard so in general the answer is NO. Some databases (Oracle, SQL Server) have come up with proprietary solutions (like also for so many other cases - the SQL standard is seriously limited in some scenarios).

At the end, though, it STILL is polling. Sorry. There is no way for the database to forward the event to the application.

In case of SQL Server, the change notification will be an entry in a message queue on SQL SERVER (using the SQL Server message broker) and you still have to poll the queue.

That said, a "long poll" can be used - you start a separae thread that asks the database for the next change.... blocking (waiting) until it happens.

Using the

WAITFOR (RECEIVE * FROM MyQueue);

style of syntax. the request will block until basicallly something is received.

Now, if you use "common" databases like MySQL - I think you are done. As in: no support. It is definitely a higher end enterprise geature.

Answer 3

You can take a look at Firebird event