How can i track any changes to Database table

Question

I have 2 applications, one to add in database, and the other to watch any changes in database,

How can i track any changes in db ( new record, Change of existing one, deleting), Is there any class like system watcher to watch Database Table?

i searched and found SQL Dependency Class, but i don't know whether it is suitable to my scenario or not.

Thanks in advance,

SQLDependency is designed to notify the middle tier of your app when changes occur (as opposed to the more traditional polling). Whether it's an appropriate choice would require more information. — EBarr, Oct 30 '13 at 12:59
The Database tracks all changes already in its transaction logs. If tracking is all you need an additional transaction log viewer might be sufficient. — Ralf, Oct 30 '13 at 13:06
http://sqlversity.wordpress.com/2013/01/29/change-data-capture/ — David Brabant, Oct 30 '13 at 14:13

score 2 · Accepted Answer · edited May 23 '17 at 12:23

2

You could use a trigger, to check the INSERT, UPDATE or DELETE queries they are executed in your instance.

Furthermore, SQL Server has a great tool, SQL Server Profiler. You can use it to connect to your instance and listen to every query and action.

This SO question maybe could help you.

edited May 23 '17 at 12:23

Community

1
1

answered Oct 30 '13 at 14:03

Alberto Solano

7,972
3
38
61

aads · Answer 2 · 2020-10-13T10:19:26.067

1

I dont recommend to go for triggers for this scenario, as it will be an extra burden.

But there are 2 options

1). Microsoft SQL Server - Change Data Capture (CDC) - here unfortunately this is NOT available in standard version. but you can find in Enterprise, Developer, and Evaluation editions

2). CodePlex-StandardCDC here

3). Change Tracking (CT) can be an option Please refer enter link description here

edited Oct 13 '20 at 10:19

answered Oct 30 '13 at 14:23

aads

2,255
4
24
25

score 0 · Answer 3 · answered Jan 08 '16 at 02:19

Use an Open-Source class SqlDependencyEx. It is pretty easy to configure and use:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Hope this helps.

How can i track any changes to Database table

3 Answers3