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
{
"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 |
Azure SQL trigger for Functions
Hope this helps.
Regards,
Jaliya
