Implementing Pub/Sub initiated from the database
Sounds like your looking for query notifications. 

Exactly as you describe, this feature allows you to subscribe to notifications which are generated when the results of a query change. Typically more efficient than repeatedly polling the database for changes.

Under the hood, notifications rely on the mechanisms used to maintain indexed views. Because of this, notification queries are constrained by the same restrictions as apply to indexed views. It's a lengthy set of requirements that you should review in detail to determine if it would make this a non-starter for your scenario.