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.
|
| 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