CODEWITHSUNDEEP
azureazure-functions
Karthik Dheeraj
Karthik Dheeraj

Reputation: 1049

Trigger Azure Function when an entry created in SQL Database

I would like understand whether we can trigger an Azure Function when an entry is created in a SQL database?

I know it's possible with a Logic App.

Upvotes: 4

Views: 6323

Answers (4)

OzBob
OzBob

Reputation: 4520

As of 2022-11-17 there is a new feature on AzFn called "trigger Azure SQL pro Functions" matched with a new AzureSQL feature called "change tracking".

"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."

Quote: Change tracking is enabled on the database and on the table:

SQL

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

ALTER TABLE [dbo].[ToDo]
ENABLE CHANGE_TRACKING;
The SQL trigger binds to a IReadOnlyList<SqlChange<T>>, a list of SqlChange objects each with 2 properties:

AzFn:

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]", ConnectionStringSetting = "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}");
            }
        }
    }
}

More samples for the Azure SQL trigger are available in the GitHub repository.

Upvotes: 3

Peter Pan
Peter Pan

Reputation: 24138

Except for using Azure Logic App with a SQL trigger as the offical tutorial Automate workflows for SQL Server or Azure SQL Database by using Azure Logic Apps said, there is the other feasible solution to create a DML trigger for a FOR/AFTER INSERT event and send a web request from SQL Database to an Azure Function with HTTP trigger.

As references, please refer to the two documents below.

  1. DML trigger statements Use the inserted and deleted Tables
  2. What? Calling an API from Database?

Upvotes: 1

rickvdbosch
rickvdbosch

Reputation: 15571

You can find all supported triggers and bindings in the Azure Functions documentation: Azure Functions triggers and bindings concepts - Supported Bindings. Azure SQL is not in the list of supported triggers.

Taken from this article,

In order to make an azure function trigger on a SQL change, there can be two possible ways.

  1. Defining Custom Binding in Azure functions
  2. If not the binding on Azure Functions side, then it can be a SQL trigger invoking an Azure Functions HTTP trigger.

Another scenario would be to change the back-end that updates the SQL and have it (also) push a message on a queue, which in turn triggers your Function.

Upvotes: 0

Sajeetharan
Sajeetharan

Reputation: 222582

Yes, Logic App has a connector for both On-Prem as well as Azure SQL. This will trigger when new rows are added

Have a look at this answer.

Upvotes: 0

Related Questions