Share via


The Workflow Tables

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.

A workflow application includes workflow tables containing all the events that implement the default workflow logic for that application. The Workflow Designer for SQL Server creates these tables and gives them a default name by taking the name of the associated user table and adding the suffix "Workflow." For example, the workflow table associated with the Customer table is named CustomerWorkflow.

Workflow tables are associated with their user tables by a designated column in the user table that stores the current state ID for each row in that table. The column is called the modStateID column, sometimes referred to as the State column. This column has a corresponding row in the modColumns table with a reference to its workflow table.

The application schema makes only one State column and one workflow table available per user table.

You can query the workflow table to find all permissible events and transitions for each row based on its current state ID. You can use these queries to write a custom workflow user interface that is similar to the one used in the Issue Tracking sample.

The schema for workflow tables is predefined. The State and NextState columns in the workflow table are bound to the lookup table associated with the modStateID column of the corresponding user table.

The following table is an example of the workflow table schema.

Column Data type Description
Id identity (int) A unique identifier for the workflow activity.
Caption nvarchar(128) Name of the event; this can be used as a button label on a form.
State int The state associated with the workflow event. For transition events, it is the initial state of the transition.
NextState int For transition events, it is the final state of the transition.
Event varchar(20) The type of workflow event.
Condition nvarchar(256) A function used to verify a complex condition, such as checking the status of subprocesses or checking values of other columns in the row — for example, Check if LoanAmount > $50. The workflow operation succeeds or fails based on this return value. If no condition is required, the column is set to Null.

For OnExpire events, the function can include a date-time expression. This column makes possible the time-based execution of the event using a generic date-time expression that applies to all items.

Action nvarchar(256) A procedure that is executed if the condition is satisfied. The function implements the events that should take place during this action. The function should return either success or an error code. If no action is required, the column is set to Null.
Script ntext The script code of the Condition and Action functions.
Created datetime Date that this event was created. Always set to the current date on an Insert action.
Note   Dates cannot be relied upon to be precise because of clock variations between computers.
CreatedBy nvarchar(64) The user who created this event. Always set to CURRENT_USER on an Insert action.
Modified datetime Date that this event was last modified. Always set to the current date on an Insert or Update action.
ModifiedBy nvarchar(64) The last user who modified this event. Always set to CURRENT_USER on an Insert or Update action.
Properties ntext A text column that makes possible the implementation of a property bag for each row. It can be used as an evaluation context for workflow optimization.
UI_Properties ntext A text column that stores a property bag for the client user interface. Its primary role is to store lists of columns that can be modified for each user interface action associated with a workflow event and the description of each user interface action.
rowguid uniqueidentifier Unique identifier used for workflow.

See Also

Workflow-Enabled Database Tables | The modColumns Table | The modObjects Table | The modObjectTypes Table | The modPermissions Table | The modProperties Table (Workflow-Enabled Database) | The modUserRoles Table | The WorkflowActions Tables | The Lookup Table