Bookshelf Home | Contents | Index | PDF     

Siebel Business Process Framework: Workflow Guide > Administering Workflow Policies > About Workflow Policy Administration >

Administering Triggers on the Workflow Policy Server

This topic describes how to administer triggers on the workflow policy server. It includes the following topics:

Creating Database Triggers

The Generate Trigger (GenTrig) component on the Siebel Server allows you to create database triggers. Workflow Policies uses database triggers to identify which records match policy conditions. Run Generate Triggers when you:

  • Create or delete new policies, including Assignment Policies, except for workflow policies that have Batch Flag set to TRUE.
  • Amend policy conditions or policy criteria.
  • Change activation or expiration dates of policies, including Assignment Policies.

To run Generate Triggers, you must have installed the Siebel Server, and the Siebel client you are using must be configured to access the Siebel Server Administration screens. For more information on installing Server Manager, see the installation guide for the operating system you are using.

CAUTION:  If you have incorrectly defined a policy condition, running Generate Triggers can result in invalid triggers. An invalid trigger can prevent execution of normal user transactions. For this reason, thoroughly test your policies in your test environment before you migrate them to your production system.

Description of Generate Triggers Execution

Table 68 describes how generating triggers works, depending on how the EXEC parameter is set.

Table 68. Description of Steps Taken to Generate Triggers, Depending on the Value of EXEC
Execution When EXEC is True
Execution When EXEC is False

Generate Trigger component automatically creates the SQL script and applies it to the server database
  1. Use the Generate Triggers component from a Siebel Server to create the SQL script file, which is placed in the root directory of the Siebel Server installation.
  2. Use your database vendor's SQL tool to execute the SQL script file against the server database.

The default setting for EXEC is FALSE.

You can run the Generate Triggers component from the Server Manager graphical user interface or command-line mode. Both the GUI and the command-line use the same parameters. The triggers are only there to create indicators for the Workflow engine to check the policies' conditions.

Example of GenTrig Behavior for a Workflow Policy With Multiple Conditions

When two or more conditions are used in a workflow policy, Generate Triggers displays the OR logic instead of the AND logic.

Table 69 describes example conditions to use to create a workflow policy based on the Account object.

Table 69. Description of Conditions Used in Example of GenTrig Behavior with Multiple Conditions
Property
Condition 1
Condition 2

Condition Field

Account Modification Num

Account Last Update By

Operation

>

<>

Value

0

0-1

Multiple triggers created for multiple conditions of one policy is expected behavior. This separates the functionality of GenTrig and WorkMon. GenTrig simply monitors database record changes and inserts records in Workflow Policy-specific tables. WorkMon evaluates violations, checks to see if the conditions associated with the rule of the violation are met, and executes the actions associated with a policy.

You cannot use AND between triggers generated for multiple conditions of a policy, because GenTrig can monitor only database changes, and database changes that violate different conditions are not always concurrent. So using an AND condition causes GenTrig to miss many violations.

For example, assume a policy has the following conditions:

  • SR area is Network.
  • Activity Priority is 1-ASAP.

Two triggers are generated. One trigger monitors an SR being created or updated, and checks if the area equals Network. The other trigger monitors an activity being created or updated, and checks whether the Priority equals 1-ASAP.

But if you use AND triggers and a user creates an SR without an activity, the trigger is not violated since the activity does not exist. If later, a user adds an activity to the SR, there still is no trigger violation because the SR record does not change. This violation is be missed due to use of AND logic. If, however, you use OR for the triggers and have WorkMon evaluate the condition, even though there are multiple violations in the S_ESCL_REQ table, WorkMon only processes one request because the other requests are not evaluated to TRUE.

About Database Triggers and Database Administration

It is important to keep your database administrators informed of active workflow database triggers, as a database Update or Insert event causes the database trigger to react, regardless of how the event is executed.

For example, if you have workflow triggers on inserts to the S_SRV_REQ table, and the database administrator does a table export and import of these records, the triggers treat every record in the database as if it is a newly inserted record, which can result in inappropriate actions being taken on old records that were simply imported again.

NOTE:  In this release, the Generate Triggers task now requires the Privileged User Name and Password instead of Table Owner ID and Password.

Running Generate Triggers

Tips you can consider when running Generate Triggers, especially if you are deleting a policy, include:

  • Deleting a policy then running Generate Triggers does not remove the database trigger. When you delete a policy, you must run Generate Triggers with the remove parameter set to TRUE. This removes every trigger. You must then rerun Generate Triggers to reset the triggers for existing policies.
  • You must stop and restart the workflow monitor agents when running Generate Triggers.
  • Generate Triggers must be rerun whenever you change policy conditions or policy groups. It is not necessary for you to rerun Generate Triggers when changing policy actions. For more information about changing the group for a workflow policy, see Moving a Workflow Policy to a Different Group.
  • For SQL Server, have your default database set correctly. To determine your default database, launch the SQL Server Enterprise Manager then navigate to the SQL Server Machine name. Next, click Security then click LOGIN. The default database is listed to the right.
  • You must start a new WorkMon task anytime you drop or regenerate triggers. This refreshes the WorkMon cache to account for the new changes in the policy being monitored.
  • When running Generate Triggers, table names have an 18 character limit. With a table for which the name has 18 characters, Generate Triggers fails with the error 18 character limit, table_name trigger fail. This is caused by a DB2 SQL limitation for trigger names being limited to 18 characters. The trigger name is derived from the table name plus a suffix such as U, I, D, U1,I1,D1, and so forth.

To Generate Triggers using the Siebel client

  1. In the Siebel client, navigate to Administration-Server Management > Jobs.
  2. In the Jobs list, click New.
  3. From the Component/Job drop-down list, choose Generate Triggers.

    This creates a new line entry but does not start the task.

  4. In the Job Parameters list, click New to modify parameter settings.

    For component-specific parameters for Generate Triggers, see Table 70. For a description of generic and enterprise parameters, see Siebel System Administration Guide.

  5. Enter your Privileged User name and password.
  6. In the Job Detail form applet, from the applet-level menu, choose Start Job.
  7. To view changes to the state, refresh the screen by clicking Run Query from the applet menu.

    Upon completion, the Status field contains Success or Error. You should view the log details.

Component-Specific Parameters for Generate Triggers

Table 70 describes component-specific parameters for Generate Triggers.

Table 70. Description of Component-Specific Parameters for Generate Triggers
Name
Value
Usage

Remove

TRUE or FALSE (default)

Set to TRUE to generate DROP TRIGGER statements to clean up the triggers. Remove does not generate CREATE TRIGGER statements.

Trigger File Name

Valid filename on the Siebel Server

Define the name and output location for the SQL script file. The default is TRIGGER.SQL. The file is created in the root directory of the Siebel Server during installation.

EXEC

TRUE or FALSE (default)

Specify a value to determine if the SQL script file runs automatically or manually.

If TRUE, the SQL script file runs automatically.

EXEC should be set to FALSE if you are running a Sybase server with any Siebel version or MS_SQL server with Siebel versions 4.x. This is performed to prevent connected users from getting an error message when Siebel generates database triggers. Make sure no one is logged in to the database before you generate triggers.

NOTE:  If you are creating a large number of triggers because there are too many workflow policies, it is recommended that the triggers be applied by the user and not the Generate Triggers server process. The Exec parameter must be set to FALSE in this case.

Mode

ALL or WORK or ASGN

Set to ALL to create both Workflow Policy triggers and Assignment Manager triggers.

Set to WORK to create only Workflow Policy triggers.

Set to ASGN to create only Assignment Manager triggers.

Privileged User Name/Privileged User Password

Assigned Privileged User name and password

Specify to make sure users enter a Privileged User name and password. The Table Owner is considered a Privileged User, so you can enter the Table Owner name and password in the Privileged User name and password fields.

Running the SQL Script File

Once Generate Triggers has finished, run the SQL script file if the EXEC parameter is FALSE.

To run the SQL script file

  1. Connect to the database server as the Siebel table owner using your RDBMS vendor's SQL tool. For example, ISQL for Microsoft or SQL*Plus for Oracle.
  2. Run the SQL script file specified by the Trigger File Name parameter. The default filename is TRIGGER.SQL. The default location of this file is the root of the directory in which the Siebel Server was installed. For example:

    C:\siebsrvr\trigger.sql

  3. Make sure no errors are reported.

For example, the policy administrator, Bill Stevens, has finished creating policies in the test Siebel client and needs the database triggers set in the Siebel database for the new policies. Using the Generate Triggers component, he sets the file output name.

This creates a file, TRIGGER.SQL, for the database administrator containing the triggers that must be modified or created in the test database for these policies.

The database administrator then runs the following command in SQL*Plus to create the triggers in the Oracle database:

SQL>@<path>\mytrig.sql

The successful creation of each database trigger in the Oracle database is indicated on the screen. For information on the syntax required for other databases, see your database documentation.

NOTE:  On an MS SQL server database, execute the script trigger.sql as the database owner, dbo, login for the Siebel database.

About Database Triggers and Remote Users

When a remote user synchronizes, the changes get incorporated into the database. For example, account information in the S_ORG_EXT table is updated on synchronization. If you run a workflow which creates database triggers that compare changes in the database against specific conditions, then the triggers fire and rows are written to the S_ESCL_REQ table if the changes are of interest to the workflow conditions during synchronization.

About the S_ESCL_REQ Table

When a trigger fires against a Workflow policy condition, a record is inserted in S_ESCL_REQ, the escalation request table. This table contains the rows in the database that can trigger a workflow policy to take action. After the workflow monitor agent processes a request, it removes the row from this table.

Frequently, because of the way triggers are created, the logic defined on a workflow policy condition is not included on the trigger itself. Also, the conditions in the trigger file might not be indicative of the policies actually being violated. When running workflow monitor agent the records in the S_ESCL_REQ table causes workflow to evaluate the conditions for the related policy. So the triggers are only there to create indicators for the workflow engine to check the workflow policy conditions.

Remedies to Address Problems Involving the S_ESCL_REQ Table

Remedies you can apply to avoid or fix problems in the S_ESCL_REQ table include:

  • Use your database tools to monitor the size and efficiency of the table. If the table becomes very large, this can indicate that too many policies are being monitored and a new workflow policies process needs to be created to share the load. If rows are being monitored and not being removed after the time interval is met, this can indicate that a policy was deactivated without removing the database triggers. The triggers are continuing to send data that is not being acted on by a workflow policies instance.
  • If creating a new business component object definition, do not set the Table property to S_ESCL_REQ. A business component cannot be based on the S_ESCL_REQ table.
  • Expire rather than delete a workflow policy. For more information, see Expiring a Workflow Policy.
  • Remove rows that belong to obsolete workflow policies. For more information, see Deleting an Obsolete Workflow Policy.
    
Siebel Business Process Framework: Workflow Guide Copyright © 2008, Oracle. All rights reserved.