Azure Functions Isolated: SQL Trigger

In this post, let's see how we can use an Isolated Azure Function to monitor a SQL table for changes.

Let's start by creating an new Azure Functions project. I am choosing a Function template as SQL trigger. 

Isolated Azure Function: SQL trigger

Once the project is created, it's going to look more or less like the below (I changed the default ToDoItem POCO class to match my table)

using Microsoft.Azure.Functions.Worker;
using Microsoft.Azure.Functions.Worker.Extensions.Sql;
using Microsoft.Extensions.Logging;
using System.Text.Json;
using System.Text.Json.Serialization;
 
namespace FunctionApp1;
 
public class Function1
{
    private readonly ILogger _logger;
 
    public Function1(ILoggerFactory loggerFactory)
    {
        _logger = loggerFactory.CreateLogger<Function1>();
    }
 
    [Function("Function1")]
    public void Run(
        [SqlTrigger("[dbo].[Users]", "SqlConnectionString")] IReadOnlyList<SqlChange<User>> changes,
            FunctionContext context)
    {
        _logger.LogInformation("SQL Changes: {ChangeSet}",
            JsonSerializer.Serialize(changes, new JsonSerializerOptions
            {
                WriteIndented = true,
                Converters =
                {
                    new JsonStringEnumConverter()
                }
            }));
    }
}
 
public class User
{
    public int Id { get; set; }
 
    public string FirstName { get; set; }
 
    public string LastName { get; set; }
}

The SQL trigger uses Change Tracking (SQL Server) functionality to monitor a SQL table for changes. And once it detects a change which can be either when a row created, updated, or deleted, the function is triggered.

So before running the project, we need to enable Change Tracking in our targeted table in the database.

-- 1. enable change tracking for the database
ALTER DATABASE ['<DatabaseName>']
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
 
-- 2. enable change tracking for the table
ALTER TABLE <TableName>
ENABLE CHANGE_TRACKING;
 
---- disable change tracking for the table
--ALTER TABLE <TableName>
--DISABLE CHANGE_TRACKING;
 
---- disable change tracking for the database
--ALTER DATABASE ['<DatabaseName>']
--SET CHANGE_TRACKING = OFF  

And now since we are going to be doing a test run of this function locally first, we need to add a special environment variable: WEBSITE_SITE_NAME to local.settings.json.

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated",
    "SqlConnectionString": "<SqlConnectionString>",
    "WEBSITE_SITE_NAME": "func-sqltriggerdemo-local"
  }
}

And now we can run Azure Function project and let's make a change to the data in the table.

SQL trigger

More read:
   Azure SQL trigger for Functions

Hope this helps.

Happy Coding.

Regards,

Jaliya