Are Programatic events possible in SQL Server

Question

Scenario: An application is started, it connects to the DB and registers itself to listen for a specific event name. Then using either a trigger or stored procedure when certain criteria are met the event may be raised. The DB then notifies all the applications that have registered that they are listening for that event. The application is then able to act and go and do some processing. This saves the program from having to constantly poll the database to see if the criteria has been met.

This scenario is possible with Interbase / FirebirdSQL but is it possible to do with SQL Server (2005 or higher) and if so what should I be looking for as I am strugling to find any information on this sort of thing. If it is not possible then is there a better way to handle this scenario to not have to poll.

Answer 1

A potential solution for this could be CLR triggers, with a data driven subscription service.

When the required update happens, the trigger catches it then looks up in the database what the applications waiting for that event are, then they are called through some common means (webservice etc.) to a standard interface.

Answer 2

Yes. You can use Service Broker notifications for this.

Though I guess the caveat in this article about not having too many clients registered will still apply. How many clients might you have awaiting notifications?