Azure SQL triggers for Azure Functions

May 27, 2023 by Anuraj

Azure Serverless Functions SQLServer

This post is about Azure SQL trigger for Azure Functions. The Azure SQL trigger uses SQL change tracking functionality to monitor a SQL table for changes and trigger a function when a row is created, updated, or deleted. Changes are processed in the order that their changes were made, with the oldest changes being processed first. Currently SQL trigger is not available as part of Visual Studio new function screen. We need to create an Azure function and modify code to support Sql triggers.

First we need to enable Change tracking in SQL Server. We can do this by executing following code. I am using the Notes database which I used in my earlier posts.

ALTER DATABASE [NotesDb]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE [dbo].[Notes]
ENABLE CHANGE_TRACKING;

Here is SQL Query execution screenshot.

Select the Automatic (preview)

For SQL trigger, we need to use SqlTrigger attribute with table name and connection string parameters for IReadOnlyList<SqlChange<T>> function parameter. Here is the code.

public static class WriteDataNotifications
{
    [FunctionName("WriteDataNotifications")]
    public static async Task Run(
        [SqlTrigger("[dbo].[Notes]", ConnectionStringSetting = "NotesDbConnection")]
        IReadOnlyList<SqlChange<Note>> noteChanges,
        ILogger logger)
    {
        foreach (var noteChange in noteChanges)
        {
            var note = noteChange.Item;
            logger.LogInformation($"Change operation: {noteChange.Operation}");
            logger.LogInformation($"Id: {note.Id}, Content: {note.Content}, " +
                $"Created By: {note.CreatedBy}, Created On: {note.CreatedOn}");
        }
    }
}

It is done. Now we can insert the data to the Notes table and we will be able to see the information in the logs.

Here are some Microsoft Learn documentation which will help us to learn more about this.

  1. Azure SQL trigger for Functions (preview)

Happy Programming.

Copyright © 2024 Anuraj. Blog content licensed under the Creative Commons CC BY 2.5 | Unless otherwise stated or granted, code samples licensed under the MIT license. This is a personal blog. The opinions expressed here represent my own and not those of my employer. Powered by Jekyll. Hosted with ❤ by GitHub