Monitor SQL Server for new row

I'm creating an application that notifies users if a new row has been added to a table in the applications database. Would it be best to create a trigger for this or just check every few seconds direct开发者_运维百科ly from the application? Thoughts?

Use Query Notifications. This is the SQL Server feature that allow notifications to be actively pushed to the client when data was changed by other user. It is better known as SqlDependency. You subscribe a query and you get notified when the result set has changed (rows were added, deleted or modified).

Eg. using a LinqToCache query and reacting to a invalidation notification:

var query = (from r in ctx.table select r).AsCached(
 "table", new CachedQueryOptions() {
    OnInvalidated = (sender, args) {
      // the query was invalidated, a new row was added!
      // refresh display or notify user
    }
 });
DisplayRows(query);

Trigger is a better idea.

how will the trigger get around the need to poll?

BTW, sql-server has a notification service - but it's probably overkill

you could use a trigger to write a very short notification record to another table, and poll that instead of the main table

A trigger is the best way to catch when a new row has been added.

However, in terms of notifying the User, this depends on how you need a user to be notified. You will still need to monitor the database. Therefore you're probably best to just keep your application as is.

You could of course have a Trigger update a value elsewhere in your DB though if the initially polling count is intensive.

A trigger is something that executes in the database, so you'd have a bit of work to do to get the trigger to send a notification back to your running application.

If you instead poll from your application (every few seconds or once a minute or whatever), you can control how often notices get sent to users. This can prevent, for example, a situation where users get thousands of emails because thousands of new rows were added.