Clever Geek Handbook

Tracking or notifying DB changes - inserts and updates are mostly

How to track or receive notifications when a record is inserted or updated in the DB? I would like to notify an external application of changes in near real time when such changes occur in the DB. Are there independent DBMS tools and application programming languages? If not, is it possible with MS Access and MS SQL Server? I try to avoid polling the DB continuously, of course.

0


source to share


4 answers


With SQL Server, it is possible to load a DLL into SQL Server itself and call methods from that with extended stored procedures. The DLL can then notify other applications - usually over a TCP socket.



+1


source


I think the latest version of Microsoft SQL Server allows you to create events in your .NET code based on server conditions and events. I have not tried and I have not heard of this "DBMS independent" way of doing it (without polling the DB every X milliseconds).



+1


source


In MS-Access, I track record changes or record additions with fields in the main table that store the user's name and the date the record was created or updated.

You need to use the windows API to capture the username, usually invoked when the switch form is opened.

I dig to find off to track specific changes. My database is used for project management. I would like to keep track of exactly what has changed, not just who and when I have.

I think this fits the requirements of the original question. I can later add a windows API that reads the username.

Closed Sub Form_BeforeInsert (Undo as Integer) Me! UserCreated = UCase (CurrentUser ()) Me! DateCreated = Now () End Sub

Private Sub Form_BeforeUpdate (Undo as Integer) Me! DateModified = Now () Me! UserModified = UCase (CurrentUser ()) End Sub

- Mike

0


source


To do this with SQL Server, you use a notification service - write a DLL that subscribes to notifications from the database for data updates that you can handle in some way.

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

Oracle has something similar (although they tend to leave their technologies in place), but I haven't seen anything neutral in the database.

0


source






More articles:


All Articles