How to track tables in SQL Server for changes
This question was asked quite a while ago, and while it covers possible solutions for SQL 2005 and 2008, it is missing a good solution for SQL 2000 that is still all too common.
I need to keep track of certain fields of a database table for changes and notify my application when those changes occur so that I can fire them up on the local network in the form of broadcast messages where any user with a client can listen to them and display them as alerts (think something similar to stock market data reaching certain thresholds).
I do NOT want to poll the database for several reasons. 1) I don't want to add additional load to the servers. 2) I would prefer to be notified in near real time rather than waiting for the polling rate to expire.
Now I can put the logic in applications that update the database, but the data can be updated from multiple sources, including the Internet, and I don't want to deal with web servers sending notifications across DMZ boundaries, etc. And I don't want to support this in 20 different applications (stronger problem).
I've seen it done on SQL 2000 using extended stored procedures and triggers, but xp seems to be tricky on cross platform and they break when installed on SQL 2005 and 2008. Maybe it's just bad code in the examples I've seen I don't sure, but I'm looking for something that works in SQL 2000 and later.
Any ideas?
EDIT:
I thought about dropping support in 2000, but that doesn't really solve my problem. I would like the solution to keep working for years. One problem with many microsoft technologies is that they are dropping support for them. For example, the notification services do what I need, but they decided to drop it in 2008 and won't be available in the next version. So I'm looking for a solution that has a good chance of sticking to.
source to share
A very simple solution
You might have a trigger that calls the webpage, update notification.
This can be pretty bad, because if the server cannot reach the Internet, it can make the insert operation quite slow for some reason. Also, depending on the frequency of insertions, it can be equally bad.
Alternative plan
In the trigger, write to the queue. (I happen to be in love with MSMQ). Then you have something waiting for this queue and you will receive messages in real time. Again, it is prone to update rates as stated above.
Best plan
You have a trigger that pushes data into the 'tblUpdatedThings' table, which you then poll. But I know you don't want a poll. Regardless, I find it better because of the reasons I describe.
source to share
You want your solution to be in the database, but you want it to be database independent. You cannot have it both ways. Pick one. If you want to be database independent, don't allow sources to write directly to the database, but call a central service that you control and where you can catch whatever events you are interested in. If you want to use the database functionality without polling, you need to deploy the code that calls the database and you will have a dependency on future versions that support your code.
source to share