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 into an existing process with no amendments. In such scenarios, 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
This can enable integration with applications where not otherwise be possible. However, you should consider the effect on database performance.SQL triggers are run synchronously. Therefore, the INSERT or UPDATE statement will wait for the trigger while all possible steps are run within Process Director.
