Frequently Asked Questions - Process Director With SQL Triggers

Starting Workflows and Processes With SQL Triggers

Many processes within a workflow system are initiated by a user action, often a completed form being submitted. However there are occasions where a process needs to be initiated from another application or system. Ideally the other system could use a web service call to start the process or possibly a simple URL to perform an HTTP POST with the relevant data. Often though, the other application may not support any of these methods or perhaps the requirement is to fit in to an existing process with no changes to it. In these cases, we have implemented systems that initiate or add data to a process via a SQL trigger.

SQL Triggers

Microsoft SQL Server is able to support triggers on both a new record INSERT and an existing record UPDATE query. In the SQL query code example below, simply insert the workflow GUID in to the relevant space and when a new record is created in tblMyTable it will launch the workflow.

Example SQL To Create Trigger

USE [Database]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgMyTable]
ON [dbo].[tblMyTable]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@vPointer INT,
@vResponseText VARCHAR(8000),
@vStatus INT,
@vStatusText VARCHAR(200)
EXEC sp_OACreate 'MSXML2.ServerXMLHTTP', @vPointer OUTPUT
EXEC sp_OAMethod @vPointer, 'open', NULL, {Line Wraps...}
{...} 'GET', 'http://{Server Name}/WD/Services/wsWorkflow.asmx/Run?WFID={Process GUID}',TRUE
EXEC sp_OAMethod @vPointer, 'send'
EXEC sp_OAMethod @vPointer, 'responseText'
EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT
EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT
EXEC sp_OADestroy @vPointer
SELECT @vStatus, @vStatusText, @vResponseText
END

Whilst this can enable integration with applications where it would not otherwise be possible, consideration should be given to the effect this may have on database performance. SQL triggers are run synchronously, therefore not only will the INSERT or UPDATE statement wait for the trigger, it will have to wait while all possible steps are run within Process Director for the process you have started.