Share via

The Workflow Engine Model

  • Article

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 workflow process is a series of states and events, the order in which they must be performed, permissions defining who can perform them, and script that is executed for each event. The workflow engine is a component in the Workflow Designer for SQL Server infrastructure that makes workflow possible.

The workflow engine is used to enforce the workflow definition and execute workflow events. When a user table is enabled for workflow, workflow-related insert, update, and delete triggers are created for that table. Any change made to an item in the table will execute the corresponding workflow trigger that in turn calls a stored procedure, which invokes the workflow engine.

The Workflow Engine Functions

The workflow engine has three functions. First, it verifies whether a change is valid for the current workflow state. For example, if there is no Delete event defined for the current state, the engine does not make it possible for users to delete an item in that state.

Second, it checks if the current user has permissions to execute the workflow event. For example, if the event is a transition from Active to Resolved, the engine checks if the current user is permitted to execute this particular event.

Third, if the event is valid and the user has execute permissions, then the workflow engine evaluates the validation script. If the validation returns True, then it executes the event. If the event completes successfully, then the engine invocation returns success, and the trigger commits the change. If an error occurs while executing the validation or event scripts, then the engine reports the error to the trigger. The trigger raises a Microsoft® SQL Server™ error and rolls back the change.

Workflow Designer for SQL Server provides three table-level options that control the way the workflow engine works. The options can be set in the Properties Pages of each workflow process, and are stored in the modObjects table.

  • **Enable workflow processing (EnableWorkflow property)   **Controls whether workflow is enforced for a given table. If set to True (default), the engine is called for each data change. Changing this property to False disables workflow processing, which makes it possible for database updates to be made that do not conform to the workflow rules. Use this setting if you want to suspend workflow processing while you make corrections to data in the table.

  • **Make deleted rows available to script (MakeDeletedRowsAvailable property)   **Controls whether original values (stored in the DELETED table inside the trigger) are made available at run time. If set to True, the data values in a row prior to a user update, delete, or insert are available to workflow script using the Session object. Then, your script can compare values in the updated row with their original values to perform validation or other processing. Set this property to False (default) to improve performance if you do not require deleted rows in your script.

  • Run workflow in a separate process (SeparateProcess property)   Controls whether the workflow engine is run in the same Microsoft® Windows® process as SQL Server. Running in a separate process isolates the workflow code from the server and yields more robust workflow applications. Running the workflow engine in the same process as SQL Server provides better performance, but errors in your workflow script could interfere with SQL Server.

    It is recommended you run the workflow engine in a separate process until you have fully debugged and tested your workflow process. The default is to run in a separate process.

    **Note   **All workflow code runs under the context of the user account you configure for the workflow engine. The permissions granted to this account on the server depend on the workflow application's requirements and the sensitivity of other data on this computer.

Schema Requirements for Workflow

Every workflow-enabled table has a status column that holds the workflow state that moves each item through the workflow process. This status column is an integer data type and has a foreign-key constraint with a keyword lookup table that stores the names of the workflow states. When you enable workflow on a table, the Workflow Designer adds this column, called modStateID, and the associated lookup table.

See Also

Planning Workflow Applications for SQL Server | Workflow Application Development Guidelines | Security Permissions Model | Database Tools and Technologies | Understanding the Workflow Process for SQL Server