Database triggers that tells my website that something has been updated

I am in the process of creating a friendlist using ASP.NET/C# and MSSQL 08. Simple datalist that lists the profile image and name of my friends.

Next to the name, I have a label showing current status of my friend. Like for instance, Online, Offile, Away etc.

My question is, how can I change the value of this label, without having a timer that calls the database all the time asking for the current status?

I would like to have the database (sql server 2008) tell me when a change as occured and tell my business logic to update the status label.

Is this possible?

Thanks!

To accomplish what you are looking for.. And this is just how I would do it, is to create a view based on the table with only the items that are needed to accomplish the task.. For instance, UserID | Online_Status.. Then using AJAX, make a call. It would be so small to the user that they would not even notice the bandwidth usage/processing... etc..etc...

This is pretty much exactly what you said you didn't want, but even if you had 1 million users and space them like 3-5 minutes apart.. You should be ok considering it would take milliseconds to perform the check.

Just my two cents..

I don't think you should do it like that. There are techniques to do this using comet but it will consume a lot of resources from your server clearly reducing the number of users that can access your site/app. The problem is that the the server and client needs to have a socket open for the server to be able to push data to the client.

What I would do is to have the client ask if there are any updates, keeping the payload to a minimum. If the server says there is data that changed the client makes another request to get that data.

You could use the SqlDependency class to get notified when the result of a database query changes. There is an excellent article on MSDN explaining the SqlDependency class. To use the SqlDependency class in the context of ASP.Net consider the strategy explained in the following video of MIX 2011.

Hope, this helps.

I believe this is what for the SqlCacheDependency is designed for. If you are using SQL Server 2005 or higher*, it implements a push-notification model from SQL Server to your application to notify you of when a change occurs in your dataset. So each time the cache is invalidated you can get the latest data, but until then it was just will read from your cached dataset and save a trip to the database. The documentation for it is here.

*However*,

As stated in the comments and such, this isn't really what SQL Server is designed for at its core, and I don't know to hand actually how efficient this solution is. If I understand your problem correctly, you would need a cache dependency PER USER which could very well be completely unscalable using this solution. Rather than second-guess what is going to be the most efficient solution, you really should develop, test, measure and find out for yourself. Every situation is going to be different, there is no "right way".

* In Sql Server 2000 and 7 it uses a pull-model.

All options given to this moment are valid ones and that's how most websites do it today; however, the OP is asking for some sort push notification mechanism as opposed to pull, and I think for that kind of thing, websockets are the way to do it.