Make C# Dependency only trigger when new rows have been inserted into MS SQL with specific column value

Question

I want to receive a "change" event when new rows have been inserted into MS SQL with specific column value.

Below is the code I currently use, which works fairly well, except that it triggers an event when any row value in [Status] column changes to/ OR from "NEW".

public void InitialiseDependencyWORK(Action onDependencyMethod)
{

    this.onDependencyMethod = onDependencyMethod;

    string sqlCommandText = "SELECT [Symbol] FROM [JJ].[Orders] WHERE [Status] = 'NEW'";

    using (SqlCommand command = new SqlCommand(sqlCommandText, conn))
    {

        Dependency = new SqlDependency(command);

        Dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }

}

void OnDependencyChange(object sender,
   SqlNotificationEventArgs e)
{
    // Handles NEW rows
}

I am only interested when a new row is inserted with [Status] = "NEW", but this event also triggers when there is no new insert BUT [Status] has changed from "NEW" to anything else.

How can I only get a trigger event when there has been a new insert?

I would like to receive a trigger event when news rows have been inserted like row 2 below:

OrderID, Status 
1,Done 
2,NEW 

I DON'T want it to trigger because row 2 has just had its Status updated - there is actually no new row to process:

OrderID, Status
1,Done
2,Done

How can I achieve this?

Answer 1

May I ask why you are reinventing SQL triggers in c# for context? https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017

The simplest solution is to capture this at the table itself and not via a query which will yield false positives.

Alternatively if you are running all of your code via a repository layer then you should be able to differentiate an INSERT from an UPDATE and pick it off there too.

Answer 2

The general use case of using SqlDependency is for detecting changes to data that doesn't change very often that you would like to cache, but also need to know if it does change so you can refresh the cache without polling the database. Your case is slightly different in that you don't really want to know when the results of that query changes... you want to know when a certain query contains results to process. The reason you're getting the notification when the status code changes to AND from "NEW" is because both of those types of changes will alter the query results. It is adding and subtracting entire rows based on both kinds of change.

If you are only using the status codes "NEW" and "DONE" and they are guaranteed to always initiate as "NEW" and progress only forward to "DONE" (and never back), then a workaround might be to use this query:

SELECT [OrderID] FROM [JJ].[Orders] WHERE [Status] <= 'NEW'

This way a new item added in status "NEW" will change the query results... but when it moves to "DONE" it will still be an OrderID returned in the query and should not trigger a change event. If you have more status values you progress though... you might consider using an integer in your status column to indicate the progression. For example 0 for new, 1 for in progress, 2 done... etc.

It sounds like you are trying to create some kind of queue of work to be done and there are other ways to do that sort of thing also. There is SQL Server Change Tracking and Data Change Tracking, Triggers, Service Broker Queues, and lots of other queuing technologies. You might check into them as well as options for your architecture.