pwarrak@iss-mea.com

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

Blog Post created by pwarrak@iss-mea.com Champion on Aug 29, 2017

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 .

 

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# ).
  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\": \"\"," +
                "\"instanceToken\": \"\"" +
            "}" +
            "}";

            streamWriter.Write(json);
            streamWriter.Flush();
            streamWriter.Close();
        }

        //Issue the Request (Start the Workflow)
        Request.GetResponse();
    }

 

If you pay attention to the above code, you will find that I am sending the inserted "UserName" variable to the workflow.

Below you can check how to add a start event variable to my NWC workflow through the "Start event" action settings:

 

 

To know how to get the body and the URL of the HTTP Request with Token, please refer to  to the blog post created by Renai Bell or click on "External start URL" in workflow settings:

 

 

 

Finally, my workflow will start and and "UserName" value is handled.

I will send and email mentioning that a new user is inserted in a table "Users" in my Database:

 

 

 

Enjoy and runs on pure nintex adrenaline

Outcomes