Tracking or Notifying DB Changes - Inserts and Updates mostly.

Question

How do I track or get notified whenever a record is inserted or updated in a DB? I would like to notify an external application of the changes in near real time whenever such changes in DB occur. Are there DBMS independent and application programming language independent ways of doing this? If not, then is it possible with MS Access and MS SQL Server in particular? I'm looking to avoid continuous polling of DB of course.

Answer 1

I think the latest version of Microsoft SQL Server allows you to raise events in your .NET code based on server conditions and events. I haven't tried it, and I haven't heard of any 'DBMS independent' way of doing this (without polling DB every X milliseconds).

Answer 2

With SQL Server it is possible to load a DLL within SQL Server itself and call methods from this with extended stored procedures. The DLL could then notify other applications - usually via a TCP socket.

Answer 3

With MS-Access, I keep track of record changes or record additions with fields in the main table that store the user name and date when the record is created or updated.

You need to use a windows API to record the user name, usually run when switchboard form is opened.

I am digging to find a away to track specific changes. My data base is used for project management. I would like to keep track of what specifically was changed, not just who and when that I have now.

I think this meets the requirements of the original question. I can later add the windows API that reads the name of the user.

Private Sub Form_BeforeInsert(Cancel As Integer) Me!UserCreated = UCase(CurrentUser()) Me!DateCreated = Now() End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer) Me!DateModified = Now() Me!UserModified = UCase(CurrentUser()) End Sub

-- Mike

Answer 4

To do this with SQL Server, you use the Notification Service - write a dll that subscribes to notifications from the DB for data updates which you can process in some way.

However, MS has said that they are removing this from SQL Server 2008.

Oracle has something similar (though they tend to leave their technology in place), but I've not seen anything that is database-neutral.