Monitoring /Watching database (activity) programtically

Question

hi guys, i'm just a newbie to database.
i was wondering how to monitor a database for changes programatically. Suppose i want to have a .net application which would run after every 100th or(nth) row insertion or row deletion or updation . how can it be achieved?
I know little about triggers.they can be used to fire executable.
But i heard that it isnt a good practice. Is there any other way?
2]Do database fire events on table updations? and can they be caught in a program?
3]Can SQL reporting services be used here?

(Also assuming that this application is independent from the actual program which does database manipulation.)

thxz

Answer 1

A trigger is really going to be your only way unless you aren't concerned about the accuracy of "100th" or "nth".

The answer to 2 and 3 are no.

You can write managed stored procedures (MSDN example) but that doesn't help you here really. In general triggers can be bad practice since they can block the initial caller but sometimes they are the only solution.

Answer 2

I think you need to question your requirement to place this low-level data monitoring in a separate application. Think about where your data changes could originate -

Do you have full understanding of every:

• stored proc within your db (now and future) and which ones update this table?
• application that may hit your database (now and future)

If not, then watching the changes right down at the data level (ie within the db) is probably the best option, and that probably means triggers...

Answer 3

SQL Server 2005 introduced query notifications, new functionality that allows an application to request a notification from SQL Server when the results of a query change. Query notifications allow programmers to design applications that query the database only when there is a change to information that the application has previously retrieved.

Check out the MSDN link for more clarity and sample immplementation

Cheers

RameshVel