Responding to Database Changes with Azure Functions SQL Triggers

SQL triggers in Azure Functions allow you to execute custom code in response to events in an Azure SQL database. This is particularly useful when you need to perform some action when data changes in a database, such as updating another database, sending an email, or creating a log entry.

Azure SQL triggers automatically execute the function whenever a row is created, updated, or deleted in the specified table. The trigger uses the SQL change tracking feature to monitor a SQL table for changes. The Azure SQL trigger feature is currently only supported on Premium and Dedicated plans of Azure Functions.

Some of the everyday use cases of SQL trigger functions are:

1. Publishing events to a message broker such as Service Bus Queue based on object state changes.
2. Updating a read-optimized database, e.g., CosmosDB, based on changes in the SQL database.
3. Performing custom operations, such as sending emails when a new customer is added.

You can learn more about Azure Function SQL triggers in the Azure SQL trigger for Functions guide.

Demo

In this demo, we will learn how to write an Azure Function that logs changes to a table.

1. Create an Azure SQL database instance named demo using the Microsoft quickstart guide.
2. Use the following script to perform three operations:

                 a. Enable change tracking on the database.

                 b. Create a table named ToDo.

                 c. Enable change tracking on the ToDo table.

-- Enable change tracking on database 
ALTER DATABASE demo 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); 
-- Create table 
CREATE TABLE dbo.ToDo ( 
    [Id] UNIQUEIDENTIFIER PRIMARY KEY, 
    [title] NVARCHAR(200) NOT NULL, 
    [completed] BIT NOT NULL 
); 
-- Enable change tracking on table 
ALTER TABLE [dbo].[ToDo] 
ENABLE CHANGE_TRACKING; 

3. Use the Microsoft guide to create a function app on the Premium and Dedicated plans. In the screenshot below, you can see the details of the function app created for the demo.

    ![Picture2\(42\)](https://cdn.document360.io/d9568273-28c0-486c-8750-64643e7f5f97/Images/Documentation/Picture2%2842%29.png)
   

   Figure 1 Create a function app

4. Refer to the Microsoft guide for Visual Studio or Visual Studio Code to create a function app project and add the following NuGet package.

dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql

5. In the project, create a function called ToDoFx as follows:

using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Sql;
using Microsoft.Extensions.Logging;

namespace Example.Function;

public class ToDoFx
{
    private readonly ILogger _logger;

    public ToDoFx(ILoggerFactory loggerFactory)
    {
        _logger = loggerFactory.CreateLogger<ToDoFx>();
    }

    [FunctionName("ToDoFx")]
    public static void Run(
        [SqlTrigger("[dbo].[ToDo]", ConnectionStringSetting = "SqlConnectionString")]
        IReadOnlyList<SqlChange<ToDoItem>> changes,
        ILogger logger)
    {
        foreach (var change in changes)
        {
            var toDoItem = change.Item;
            logger.LogInformation($"Change operation: {change.Operation}");
            logger.LogInformation($"Id: {toDoItem.Id}, Title: {toDoItem.title}, Completed: {toDoItem.completed}");
        }
    }
}

public class ToDoItem
{
    public Guid Id { get; set; }
    public string title { get; set; }
    public bool? completed { get; set; }
}

6. Any change to the ToDo table triggers the function, which logs the entity's current state before exiting. It expects the SQL connection string to be specified in the application setting parameter named SqlConnectionString. Ensure the connection string is provided in your function app before deployment to avoid errors. Microsoft Azure's Manage your function app guide explains how to work with application settings in a function app.

7. Use the previous guide specific to your IDE to publish your function app to Azure.