Azure Functions Trigger for SQL DML Operations

Question

All, I am looking for suggestions on Event based approach to trigger a DML operation.

Can we use Az Functions to invoke the DML operation in DB1.schema.TableA upon operation in DB2.schema.TableA ?

Here is the env details:

SQL Env - SQL Managed Instance.

I am open on any idea for event based approach.

Thank you!

Answer 1

https://learn.microsoft.com/en-us/azure/azure-functions/functions-bindings-azure-sql-trigger

You can use Azure Functions to trigger SQL DML operations. Azure SQL trigger for Functions, which is currently in preview, allows you to do this. This 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.

an example

using System.Collections.Generic;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.WebJobs.Extensions.Sql;

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

it's important to note that the Azure SQL trigger for Functions uses a polling loop to check for changes. If you have a high frequency of changes, this might impact the performance of your Azure Function and potentially increase costs as Azure Functions use a consumption model where costs are tied to execution times.