monitor table in data base

Question

hi all

i want to make a web site to act as a monitor to a certain table in data base or act as a listenter on that table

eg lets say i have table employees

i want to make a web page that listen to changes occurs on that table (all DML operations)

whenever a record is inserted i want this page alert me that a "1 row is inserted in table employee",or updated i want to get an alert " row number xx is updated" and so on

so what is the best practise to do so

thnx

Answer 1

You can't only make a web page the "listens" because pages only execute their code on demand. Something has to access the page for it to execute (you can have the page refresh it self, but you have to have it open in a browser, or being accessed by a program). You'll either need to create a service, or a scheduled task which either pings the database and checks for changes, or pings the web page to check the database.

Edit: Here is what I'm trying to say.
You have two types of notifications, push vs. pull. Accessing information from a web page is a pull notification. You have to access the page to get the information. As long as you are accessing the page, you can continue to receive updates, but when you stop, you will no longer be notified when something changes.

This is contrast to something like sending an email every time something. You can have a service which sends off a notification to other systems when the table is updated (pager, email etc.). A web page can't do this on it's own.

If you want a pull notification a web page can do that no problem, but when you stop accessing the page, you won't get notifications about the updates. If this is what you want, just have either the page refresh, or an ajax request ping some server side code which checks the db, but this approach doesn't work for something that you absolutely need to know about when it occurs.

Alternatively, you could create a trigger in SQL server which sends out a notification every time the table is updated. If you want to access the information from a page, this isn't the way to go, but if you want to be notified say be email, then you can have the trigger fire off one. (this is for a solution when you need to have a push notification). You could have a trigger update a web page if you implemented a comet solution, but there are probably easier ways.

http://www.codeproject.com/KB/aspnet/wxv_comet.aspx

Answer 2

Part 1: db trigger.

This is the key part that will make this solution possible/simple/robust. You will need to create a db trigger that writes to another table (tblEmployeeChanges) each time the employee table has an update or insert. So, if in a particular minute their were 5 changes to the employee table, tblEmployeeChanges would have new 5 records, kindof like this:

EmployeeChangeID  ChangeType  EmployeeID  ChangeTime
1643              Insert      434243      2010-07-23 09:14:04
1644              Update      345345      2010-07-23 09:14:07
1645              Insert      345347      2010-07-23 09:14:21
1646              Update      345438      2010-07-23 09:14:39
1647              Update      435634      2010-07-23 09:14:41

You monitoring web page would then check this table every couple seconds, or however often you wish (when the user hits the "refresh" button maybe), your web page will get this data and show it.

This table might get really big, but would remain pretty efficient with good indexes. You could easily purge the "old data" now and then. You would have the clustered index on EmployeeChangeID or ChangeTime, so constant additions should not be, and querying should not be a big problem.

Part 2: simple web REST service

Make an .asmx page or simple wcf service that takes an EmployeeChangeID, and sends down information for each tblEmployeeChanges record that has an EmployeeChangeID greater than that record.

Part 3: repeating Ajax call** from your monitoring page.

Every, say, 2 seconds, your web page page sends the last EmployeeChangeID that the page knows about. If there are any new rows, your callback function adds them to the display (for the human to see), does a beep or something, and writes the ID of the last change record in the list to a javascript variable or hidden input, to use in the next ajax call (2 seconds later).

Example:

So, at 9:14:38 your page asks for all changes since change 1645, and gets back nothing. Then at 9:14:40, (for change 1646); your page shows that data. Then at 9:14:42 your pages asks for every change after 1646, get's back nothing, etc.

Note on trigger as key to solution:

I don't particularly like triggers, and sometimes go for months without using them. In this case though, it's necessary. Note if you really need to keep track of changes in your system, it's better to have an architecture where every change is kept track of for all values, and each property of each employee is kept in a key value pair table where the latest value is considered the "active" value. This architecture has advantages and disadvantages, and might not be right for you. But if the architecture considers tracking changes to be an afterthought, then tracking changes will have to be handled inelegantly, as an afterthought, with tools like db triggers.

Answer 3

SQLDependency is what you could be looking for.