How can we determine which column has changed when an Azure SqlTrigger hits

Question

This is my code

[FunctionName("Function")]
public  async Task Run([SqlTrigger("s_sample", "ConString")] IReadOnlyList<SqlChange<ToDoItem>> changes,
                       ILogger log)
{
    var myList = new List<object>();
    string sampleId="";

    foreach (SqlChange<ToDoItem> c in changes)
    {
        ToDoItem toDoItem = c.Item;
            
        log.LogInformation($"SQL Operation:{c.Operation}");
    }
}

After updating a record I want to determine which columns are getting updated.

Is there any way we can check what are the old values and what are newly updated values?

Answer 1

In order to use SqlTrigger, you had to enable the change tracking in your database. It's the way to figure out the columns that got changed. As far as I know, there's no way to magically get them directly in your function, you will need to query the database in order to figure this out. A complete explanation about how Chage Tracking works and how to get the changes, can be found in the following link:

https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver16

EDIT:

to get values which changed, I'm afraid you'll have to use Temporal Tables, but even then, you will need to iterate and compare against current values to figure out if they are the same or not.

You can find how to do it in here: How to identify changed values using a SQL Server temporal table?