Call external API on change to Azure SQL database table

Question

We have a table called Guest in an Azure SQL database. We also have a campaign management tool sitting behind an API on the providers cloud.

When a record is created, updated or deleted in the Guest table, we would like to call the API in order to update the campaign management tool with the latest information about the Guest.

Our initial idea was to hook up a database trigger to a C# .NET Azure Function, however, it looks like this is only supported in Cosmos DB.

We would prefer not to have an application running on a scheduled task that periodically checks for changes in the database and sends these changes to the API.

We have also been reading about creating CLR stored procedures but it looks like these are not supported in Azure SQL databases.

Looking forward to hearing ideas & suggestions.

Answer 1

I can think of a few ways to accomplish this.

[Unfortunately CLR is no longer supported in SQL Azure.]

One way is:

1. Turn Change Data Capture on, on your Guest table.
2. Create a server-less Azure Function that has a timer trigger. This function would use the CDC to determine what had changed in your table, and call your vendor API accordingly.

The server-less function is relatively lightweight compared to "an application running on a scheduled task".