Triggering "NWC" when an event occurs in Microsoft SQL Server

  • 30 August 2017
  • 4 replies
  • 12 views

Badge +7

Dears,

 

Many Nintex customers working on Microsoft SQL Server are asking me how to trigger a Nintex Workflow (NWC) when an item/row is added to the database.

 

After looking at the "Start event" action settings in my NWC, Microsoft SQL Server connector is missing:

 

NWC Start event configuration

 

 

On the help portal for NWC, you can go to the following page related to Microsoft SQL Server connector and see that the feature is not yet available:

 


 

One of the solution is to use the "External Start" event:

 

 

 

You can refer to the blog post created by Renai Bell in order to provide a REST URL for the workflow:

 

Nintex Workflow Cloud External Start.

 

But still the challenge lays in the below:

How to call the above generated URL when an item is added to a table in my SQL database?

For sure it will not be a Transact-SQL trigger happy.png.

 

Let's note that in addition to the Transact-SQL language, we can use .NET Framework languages (C# for example) to create database triggers.

 

Among the benefits of using .NET Framework languages are:

  1. Language Richness by owning capabilities previously unavailable to SQL Server developers (We are using C# happy.png).
  2. Debugging database triggers from Microsoft Visual Studio.

 

I will go for Common Language Run-time Integration (Commonly known as CLR) and create a trigger in .Net managed code. It will be executed like any other Transact-SQL trigger once an item is added to the database.

 

For more info on how to create a trigger in .Net managed code, check the below:

 

How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration

 

Below you can see my C# code for triggering event on item added to a table:

 

[SqlTrigger(Name = "UserNameAudit", Target = "Users", Event = "FOR INSERT")]
public static void UserNameAudit()
  {
   SqlTriggerContext triggContext = SqlContext.TriggerContext;
   SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);

   if (triggContext.TriggerAction == TriggerAction.Insert)
    {
     using (SqlConnection conn = new SqlConnection("context connection=true"))
      {
       //Get UserName from inserted
       conn.Open();
       SqlCommand sqlComm = new SqlCommand();
       SqlPipe sqlP = SqlContext.Pipe;
       sqlComm.Connection = conn;
       sqlComm.CommandText = "SELECT UserName from INSERTED";
       userName.Value = sqlComm.ExecuteScalar().ToString();

       //Launch the external Start
       StartNWC(userName.Value.ToString());
      }
    }
  }‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

 

The above code will create a trigger named "UserNameAudit" associated to a table named "Users" on item inserted.

I will retrieve the value of the column "UserName" from the inserted item and then "send it" to my NWC in order to start.

 

Let's have a look on the StartNWC function where I will trigger my NWC by issuing an HTTP Request:

 

    public static void StartMyNWC(string UserName)
    {

        //Build HTTP Request
        HttpWebRequest Request = (HttpWebRequest)WebRequest.Create("Workflow URL with Token");
        Request.Method = "POST";
        Request.ContentType = "application/json";

        //Build the body of the Request
        using (var streamWriter = new StreamWriter(Request.GetRequestStream()))
        {
            string json = "{" +
            """startData"": {" +
                """se_user_name"": """ + UserName + """" +
            "}," +
            """options"": {" +
                """callbackUrl"": """

4 replies

Badge +7


Badge +2

Very Good post indeed.

now SQL Server can call NWC workflows and also pass parameters.

Way to go Philip

Badge +1

Great post Philip - it's great to see Nintex Workflow Cloud's external start being put to interesting use.

 

I would like to highlight that your solution works well to demonstrate external start's extensibility, but would recommend some changes before it was put into a high transaction scenario.

 

Database triggers should be lean, efficient and return as fast as possible and therefore there should not be a hard dependency between your trigger and a web call. If there are a significant number of transactions on the database and any latency in web calls, this could cause tables to lock up and compromise the concurrency of your database.

 

Some modifications such as using the database trigger to create a separate record in a processing table or a queuing service that then does the web service call would work well. This can guarantee that your database table is not locked while web service calls are made.

Badge +7

Hi David Fitzpatrick‌,

Yes absolutely right.

It was simply a demonstration showing the feasibility of NWC triggering through SQL events.

This article will be followed soon by another related one showing the usage of Microsoft Azure Service Bus for handling the queuing of "bulk" inserted Item.

BR,

Philip

Reply