Scripting Workflow Events for SQL Server

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The built-in functionality of workflow events can be enhanced using Microsoft® Visual Basic® Scripting Edition (VBScript). Because the script within a workflow process is executed on the server, the compatibility of VBScript with Web browsers is not a consideration.

Within each event, you can add script to fulfill the requirements of your workflow application. To extend the capabilities of your workflow beyond the scripting language, you can invoke stored procedures from within your script. For example, you can call a stored procedure that uses the Microsoft® ActiveX® Data Objects (ADO) connection and Session object to work with data.

Scripting in the Workflow Designer is done in an editor called the Code Editor that features statement completion, drag-and-drop support, color-coding, Microsoft® IntelliMouse® support, script verification, and other enhancements.

When writing script for a workflow process, you must be familiar with the available events and the order in which they are executed. In addition, it is critical to understand how these events loop and the consequences of changing the names of script procedures.

For information about	See
How script is formatted in Code Editor	Script Procedures
How script is triggered	Workflow Events
The order in which workflow events occur	Order of Events
How loops in the workflow process are interrupted automatically	Loop Suppression
How script procedures are named	Names of Script Procedures

Script Procedures

A single action, such as moving from one state in the workflow process to another, can trigger several different events, which occur in a particular sequence. For each of the workflow events, there are two associated script procedures — a validation script procedure and an event script procedure. Each is available as a separate script block in the Code Editor. For example, you can write four script procedures associated with states: OnEnter, OnEnterValidate, OnExit, and OnExitValidate. The validation script procedure must return a value of True for the associated event and event script procedure to be executed.

Workflow Events

There are six events: OnCreate, OnDelete, OnEnter, OnExit, OnChange, and OnExpire. These events serve two purposes — they represent valid events in your workflow process, and they are used to trigger scripts. All the events except OnExpire are triggered when records are saved.

For example, if you want to design a workflow process where a new record can be set to a state named New or a state named Review, each of these states must include a transition from the Item Created shape, which contains an OnCreate event by default. Similarly, if you want to make it possible for a record to be deleted regardless of its current state, you must add a transition to the Item Deleted shape, which includes the OnDelete event, to each state in your workflow process.

OnExpire events are regulated by the SQL Server Agent and are executed at a designated time defined by that agent. All the other events are triggered by user interaction with the data.

Note Any event can be used to generate a state change using script.

For each of these events, there is a validation script procedure and an event script procedure. The validation script procedure must return a value of True for the event script procedure to be executed. In addition, during transitions, all validation script procedures that are defined must return True before any event script procedure is executed.

For example, take the case of a state transition from Open to Resolved. If you have OnExit and OnEnter events defined for Open and Resolved respectively, the validation script procedures for OnExit of Open, OnChange between Open and Resolved, and finally OnEnter of Resolved must all return True for the corresponding event script procedures to be executed. Only after True is returned for all of the validation script procedures, will the event script procedures occur in the same order: OnExit of Open, OnChange between Open and Resolved, and finally OnEnter of Resolved.

**Note **OnEnter and OnExit are events that fire each time a state is entered or exited regardless of the transition that caused the entry or exit.

By default, each validation script procedure returns True. For example:

Function State1_OnEnterValidate()
    State1_OnEnterValidate = True
End Function

You can modify the validation script procedure to make it conditional. For example:

Function State1_OnEnterValidate()
   If CheckValue = 1 then
       State1_OnEnterValidate = True
   End if 
End Function

If the CheckValue variable is not equal to one, the event script procedure will not be executed. In validation script procedures, CheckValue can be a variable, an item from the Session object, or a call to a function.

The following example uses the Session.Item object to look at a date field called DateModified. If it is equal to today's date, then the validation script returns a value of True.

Function State1_OnEnterValidate()
     If session.item("DateModified") = now Then
        State1_OnEnterValidate = True
     Else
        State1_OnEnterValidate = False
    End If
End Function

Order of Events

For each of the three transition events — OnCreate, OnChange, and OnDelete — additional events are executed in a certain order (for more detailed information, see Order of Workflow Events).

OnCreate

OnCreate Þ OnEnter

The OnCreate event executes when the user inserts a record in the database, followed by OnEnter of the designated first workflow state.

OnChange

OnExit (State A) Þ OnChange (State A to State B) Þ OnEnter (State B)

When the user initiates a transition from one state to another or from a state back to the same state, three events are triggered: OnExit from the first state, OnChange for the transition, and OnEnter of the next state.

OnDelete

OnExit (State B) Þ OnDelete

When a user deletes a record from the database, the OnExit event of the final state executes, followed by OnDelete.

Loop Suppression

If you make a transition from one state to another, the associated OnExit, OnChange, and OnEnter events will be evaluated. Because you might have code within the OnEnter event of the next state to evaluate and transition it to yet another state, looping could occur. Because looping indefinitely can cause Microsoft® SQL Server™ to hang or crash, Workflow Designer for SQL Server implements loop suppression by caching all the states the record has transitioned to during the current transaction context. When it is detected that you have been in a state previously, the transition to the state is made possible, but the loop is broken, because any subsequent OnExit, OnChange, and OnEnter events are not executed.

For example, if you have a workflow process that enforces the following transitions:

State A Þ State B Þ State C

An issue in State A is moved to State B.
The OnEnter event of State B triggers code that evaluates a condition and moves to State C.
The OnEnter event of State C triggers code that evaluates a condition and moves back to State A.
The OnEnter event of State A triggers code that evaluates a condition and moves to State B. At this point, you have looped back to your starting point.
Although you entered State B previously during the transaction context, the entry is made possible, and the OnEnter event is executed. The OnEnter of State B evaluates and moves to State C again.

The loop is broken here. The OnExit of State B, OnChange from B to C, and OnEnter State C are not executed.

So, you loop completely through the events, and only the first event is executed twice.

Names of Script Procedures

Script procedure names in the Code Editor follow the convention of combining the Name property of the workflow object with the name of the event by means of an underscore. The validation script procedures append Validate to the end of the name. For example, the event script procedure for the OnExit event of a state named State1 is named State1_OnExit. The validation script procedure is named State1_OnExitValidate.