title: Detect specific conditions database change
body: <p>I'm making a simple chat in my application using ASP.NET MVC 5 and SQL Server 2016.
  Got the following table in database:</p>
  
  <pre><code>[dbo].[UserMessages](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [UserIdSender] [int] NOT NULL,
  [UserIdReciever] [int] NOT NULL,
  [DateSent] [datetime] NOT NULL,
  [MessageText] [nvarchar](max) NULL,
  [IsRead] [int] NOT NULL
  </code></pre>
  
  <p>So, when one user sends a message [UserIdSender] to another user [UserIdReciever], the second will see it when he opens his chat tab as the following request to db will be done:</p>
  
  <pre><code>public IQueryable&lt;UserMessages&gt; GetDialogByUserIds(int senderId, int recieverId)
      {
          return _msgRepo.GetAllQueryable(m =&gt; (m.UserIdSender == senderId &amp;&amp; m.UserIdReciever == recieverId) ||
                                               (m.UserIdSender == recieverId &amp;&amp; m.UserIdReciever == senderId));
      }
  </code></pre>
  
  <p>The question is to hold live chat, so when second user answers to first, the first one will see it immediately. So, I'm thinking about is there a way to create a specific event in sql when a specific table is changed
  Something like this in sql:</p>
  
  <pre><code>if(UserMessages.IsChanged())
  {
       event UserMessagesChanged();
  }
  </code></pre>
  
  <p>Or is there any better way to notify user of incoming messages, to not sending a request every time database will be changed as there are many users and this event will fire every time any user sends a message?</p>