Azure Function Apps: Trigger in Azure SQL /SQL Server to execute Azure Function

Ever thought of SQL (on VM or On-Premise server) Input Trigger for the Function app?

There is no predefined input trigger for SQL in Azure functions. Which means there is no way to trigger an Azure function on any change in the SQL data. Triggers mainly at present are Blob, Queue, Table, Eventhub, Http, Timer etc.

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.

The first option is quite tricky and may need a proper understanding of how Azure functions existing bindings are defined and how they work. The second option is not a solution rather it's a workaround but easy to implement.

In order to make an HTTP call from SQL, we need to make use of the concept of CLR assemblies in SQL Server.

So how it works is we create an assembly having methods to make HTTP calls in Visual Studio and register this assembly in SQL Server. Once this assembly is registered we can use the method inside the assembly to make a call to function app HTTP trigger.

Let's take a closer look step by step.

In the below code I have created two methods "POST" and "GET" to make HTTP calls.

 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString HttpGetFromSQL(SqlString uri)
{
    SqlString document = SqlString.Null;
    WebRequest req = WebRequest.Create(Convert.ToString(uri));
    try
    {
        using (WebResponse resp = req.GetResponse())
        {
            using (Stream dataStream = resp.GetResponseStream())
            {
                using (StreamReader rdr = new StreamReader(dataStream))
                {
                    document = (SqlString)rdr.ReadToEnd();
                }
            }
        }
    }
    catch (Exception ex)
    { }        
    return (document);
}

 [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString HttpPostFromSQL(SqlString uri, SqlString postData)
{
    SqlString document=SqlString.Null;
    try
    {
        byte[] postByteArray = Encoding.UTF8.GetBytes(Convert.ToString(postData));
        WebRequest req = WebRequest.Create(Convert.ToString(uri));
        req.Method = "POST";
        req.ContentType = "application/x-www-form-urlencoded";
        using (Stream dataStream = req.GetRequestStream())
        {
            dataStream.Write(postByteArray, 0, postByteArray.Length);
        }
 
        using (WebResponse resp = req.GetResponse())
        {
            Stream dataStream = resp.GetResponseStream();
            using (StreamReader rdr = new StreamReader(dataStream))
            {
                document = (SqlString)rdr.ReadToEnd();
            }
        }
    }
    catch (Exception ex)
    { }
    return (document);
}

NOTE: For the integration of this code with SQL Server we need "Microsoft.SqlServer.Server" assembly.

Once we have the assembly ready we can enable the SQL Server to run the CLR code.

 EXEC sp_configure 'clr enabled';
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

In order to enable the SQL Server to access the resources outside of the SQL Server, we need to mark the database as TRUSTWORTHY ON. This is needed to run the assembly which we will register to have the "EXTERNAL ACCESS".

 ALTER DATABASE TestDB SET TRUSTWORTHY ON;

Now let's register the assembly with the SQL Server.

 USE TestDB
GO
 
CREATE ASSEMBLY FunctionAppReq
FROM 'C:\Amit Data\FunctionAppClassLibrary.dll'
WITH PERMISSION_SET=EXTERNAL_ACCESS;
 
GO

Once the Assembly is registered you can call the specific functions of the assembly in a SQL Function.

 CREATE FUNCTION dbo.get_web_request(
     @uri        nvarchar(max)
)
RETURNS nvarchar(max)
AS
EXTERNAL NAME FunctionAppReq.Functions.HttpGetFromSQL;
 
GO

Once this function is ready we can now create any trigger on any table to call this function and in turn call the HTTP trigger of the azure functions.

 CREATE TRIGGER StudentsAfterInsertTrigger ON TestDb.dbo.Students
AFTER INSERT  
AS  
begin
 
 declare @str varchar(max);
 set @str = dbo.get_web_request('http:\\functionapptest.azurewebsites.net\api\httptriggerfunction?param1=val1$param2=val2');
 
end

Comments

• Anonymous
  January 10, 2018
  Many of us have been looking for this particulate trigger , and found a indirect way to achieve it , thanks
• Anonymous
  January 11, 2018
  Didn't know it can be this easy.. thanks for the workaround. still waiting for functions trigger like the existing ones blob, queues
• Anonymous
  January 15, 2018
  Azure SQL doesn't support CLR Assemblies. It will work only in SQL server.
  • Anonymous
    January 15, 2018
    Agree and I actually meant the SQL on Azure VM or an on Premise SQL. Made the correction in the post. Thanks for pointing that out.
• Anonymous
  January 15, 2018
  Azure SQL doesn't support CLR Assemblies, this can be helpful only in SqlServer.
• Anonymous
  January 20, 2018
  The comment has been removed