Request to Support Before Changes on a SQL Trigger

[phildaniels]

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

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<SqlChange<Table>> 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.

[Charles-Gagnon]

@phildaniels Unfortunately I don't know of a way this is supported with change tracking itself. The only value we get from CHANGETABLE is the primary key values that were modified - which means that we don't have access to previous values for other data in the table (only the current state).

Theoretically if we were to keep our own copy of the table in the function itself that would allow us to make these comparisons and return the previous values - but that's likely not something that we'd be able to do for performance reasons.

We're putting this into the backlog for now since the idea itself is good so if we can find a way to do this that doesn't add a lot of overhead we'll gladly take it into consideration.

[sam-masscreations]

+1 on this, would like something similar to how AWS handles this of a before and after object seen here https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Streams.html