Database Modification (Insert/Update/Delete) Event Notifications

Hello,

I am new to SQL Server and am building a C# application which will use a local SQL Server Express 2005installation. My application will be interacting with a DB Schema with approx 250 tables and the schema is already defined. My application is responsible for monitoring changes to the data in the DB. It must watch for all data changes in the database (Insert/Update/Delete). I am looking for a way to monitor changes to the DB data due to Insert/Update/Delete activity.

Is there a simple way in SQL Server for my application to receive events whenever the table content is modified. I have come up with a few ideas myself, for example adding trigger to each table to update an additional Event table that I could look at or even receive an Query Event on , but this swould mean creating and maitaining 750 triggers (250 tables x 3).

I can't help but feel there must be a simpler way, such as some type of SQL Audit or Monitoring feature that is already built in that could notifiy an application whenever there are DB data changes? Does such a feature exist? What would be the best way to accomplish this?

MarKGB

Trigger is invented mainly for this. If you have to audit every table, you could combine insert/update/delete event into 1 trigger. That will result in 250 triggers.|||

Hi,

if you are using SQL Server 2005 you can use the new query notification for getting the notification of changes to the database. In SQL Server 2000 I would suggest to go the way you already mentioned. Creating an event table and filling this via triggers based on the tables. Perhaps you can create a generic autiting trigger (Look in google for that there have been some samples arounf how to implement this.)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Great!, I didn't know you could combine the events to one trigger. That will certainly help reduce the amount of coding. Thanks!

Mark

|||

I did as you suggested and googled 'generic audit trigger' and got a lot of hits. In fact, I found a nice example that uses a generic CLR trigger and was able to get it working. The example can be found here:

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

I think using this in combination with a Query Event will give me what I am looking for.

Thanks for your help.

Mark