Azure / Azure/azure-functions-sql-extension

Request to Support Before Changes on a SQL Trigger

Open
#650 2 comments 0 reactions 0 assignees View on GitHub
enhancement trigger
Dominant language
C#
Stars
130
Forks
71
Avg merge
4d 8h
Merged PRs (30d)
4

Description

A nice feature would be, on the SQLTrigger, having the ability to have a "Before" Object that represents the state of the object before the update. IE

```csharp
using System.Collections.Generic;
using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.WebJobs.Extensions.Sql;
using Newtonsoft.Json;

namespace AzureSQL.Table
{
public static class TableTrigger
{
// before running SQL Statement: [ID] - 1, [SOMETHING] - 'Something'
// running SQL Statement: UPDATE [TABLE] SET [SOMETHING] = 'Something Else' WHERE [ID] = 1
// after running SQL Statement: [ID] - 1, [SOMETHING] - 'Something Else'
[FunctionName("TableTrigger")]
public static void Run(
[SqlTrigger("[dbo].[Table]", ConnectionStringSetting = "SqlConnectionString")]
IReadOnlyList> changes,
ILogger logger)
{
foreach (var change in changes)
{
Table before = change.Before; // of type TABLE
logger.LogInformation($"Change operation: {change.Operation}"); // `Change operation: 1` // Enum Value `Update`
logger.LogInformation($"Before: {JsonConvert.SerializeObject(before)}"); // ` { "id": 1, something: "Something Else" } `
}
}

public class Table
{
public int Id { get; set; }
public string Something { get; set; }
}
}
}
```

For deletes the before could be the state of the record before deleted, and for inserts it could just be null

I believe there is capability of querying this data from the change tracking in SQL server.

A big use case I see of this trigger is for event sourcing, IE publishing events of "what changed" on the records in our tables, but this would be easier to do if, for updates, the state of the record as it was before update was present. My current workaround for this is to keep an audit table of changes which is not ideal.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.