Siebel Business Process Framework: Workflow Guide > Administering, Testing, and Migrating Workflow Policies > Administering Workflow Policies >

Administering Database Triggers on the Workflow Policy Server

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

• Overview of Creating Database Triggers
• Configuring Database Triggers
• Guidelines for Configuring Database Triggers
• Using Database Triggers with Remote Users
• Managing Database Triggers and Database Administration
• Fixing Problems in the S_ESCL_REQ Table

Overview of Creating Database Triggers

The Generate Trigger (GenTrig) server component on the Siebel Server allows you to create database triggers. Workflow Policies uses these triggers to identify the records that match workflow policy conditions. You can run Generate Triggers if you must do one of the following:

• Define or delete new workflow policies, including assignment policies, except for workflow policies with the Batch Flag property set to TRUE.
• Amend workflow policy conditions or policy criteria.
• Change activation or expiration dates of policies, including assignment policies.

How Generate Triggers Works with a Workflow Policy That Contains Multiple Conditions

If a workflow policy includes two or more workflow policy conditions, then Generate Triggers uses OR logic instead of AND logic.

Table 65 describes example workflow policy conditions that Siebel CRM uses to create a workflow policy that references the Account object.

Siebel CRM can use multiple database triggers for multiple workflow policy conditions in one workflow policy. This configuration keeps Generate Triggers functionality and Workflow Monitor Agent functionality separate:

• Generate Triggers monitors changes that Siebel CRM makes to database records and inserts records in tables that are specific to a workflow policy.
• Workflow Monitor Agent evaluates conditions, determines if the conditions that are associated with the rule are met, and runs the actions that are associated with the workflow policy.

Using an AND Condition with Multiple Database Triggers

If multiple workflow policy conditions exist in a workflow policy, then you cannot use an AND condition between database triggers. Generate Triggers can only monitor database changes. Database changes that meet different conditions might not be concurrent. Using an AND condition can cause Generate Triggers to miss many conditions.

For example, assume a workflow policy contains the following workflow policy conditions:

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

In this situation, Siebel CRM creates the following database triggers:

• One database trigger monitors a service request that Siebel CRM creates or updates, and then determines if the area equals Network.
• One database trigger monitors an activity that Siebel CRM creates or updates, and then determines if the Priority equals 1-ASAP.

If you use an AND database trigger, and if a user creates a service request that does not include an activity, then Siebel CRM does not run the database trigger because the activity does not exist. If the user then adds an activity to the service request, then no database trigger runs because the service request does not change. The AND condition causes Siebel CRM to miss this violation. If you use an OR condition, and if Workflow Monitor Agent evaluates the workflow policy condition even though multiple violations exist in the S_ESCL_REQ table, then the Workflow Monitor Agent only processes one request because the other requests do not evaluate to TRUE. For more information, see Tables That Workflow Monitor Agent Uses.

Configuring Database Triggers

This topic describes how to configure the Generate Triggers server component. You can configure this server component from the Siebel client or from the command line. The Siebel client and the command line use the same parameters.

CAUTION:  If you incorrectly define a workflow policy condition, then running Generate Triggers can result in an invalid database trigger. An invalid database trigger can prevent Siebel CRM from processing normal user transactions. It is recommended that you thoroughly test your workflow policies in a test environment before you migrate them to a production environment.

To configure database triggers

1. Make sure your environment can run the Generate Triggers server component:
   1. Make sure the Siebel Server is installed.
   2. Make sure the Siebel client can access the Siebel Server Administration screens.

      For more information on installing Server Manager, see the installation guide for the operating system you are using.

2. In the Siebel client, navigate to the Administration-Server Management screen, and then the Jobs view.
3. In the Jobs list, click New.
4. In the drop-down list for the Component/Job field, choose Generate Triggers.

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

5. In the Job Parameters list, click New to modify component specific parameters, using values from the following table.

   Name	Value	Description
   Remove	TRUE or FALSE (default)	To create DROP TRIGGER statements to clean up the database triggers, set to TRUE. Remove does not create 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 value is TRIGGER.SQL. Siebel CRM creates this file, and then places it in the root directory of the Siebel Server during installation.
   EXEC	TRUE or FALSE	Set to TRUE to run the SQL script file automatically. Set to FALSE to run the SQL script manually. For more information, see Using the EXEC Parameter.
   Mode	ALL, WORK, or ASGN	Use one of the following values: ALL. Creates database triggers for workflow policies and Assignment Manager. WORK. Creates database triggers only for workflow policies. ASGN. Creates database triggers only for Assignment Manager.
   Privileged User Name and Privileged User Password	Assigned Privileged User name and password	To make sure the user enters a Privileged User name and password, you can define the user name and password. Siebel CRM considers the Table Owner as a Privileged User. You can enter the Table Owner name and password in the Privileged User name and password fields.

   For a description of generic and enterprise parameters, see Siebel System Administration Guide.

6. Enter the Privileged User name and password.
7. In the Job Detail form, choose the applet-level menu, and then choose Start Job.
8. To view changes to the state, choose the applet-level menu, and then click Run Query to refresh the screen.

   The Status field contains Success or Error after Siebel CRM refreshes the screen. You can view the log details.

9. If EXEC equals FALSE, then you must manually run the SQL script file.

   For more information, see Manually Running the SQL Script File.

Using the EXEC Parameter

The EXEC parameter specifies how to run the SQL script file automatically according to one of the following values:

• TRUE. Generate Triggers automatically creates the SQL script and applies it to your database.
• FALSE. You must manually run the SQL script file

If one of the following situations is true, then you must set EXEC to FALSE, you must manually run the SQL script file, and you must not use the Generate Triggers server component. For more information, see Manually Running the SQL Script File:

• You run a Sybase server with a Siebel or MS_SQL server. Setting EXEC to false prevents a connected user from receiving an error message when Siebel CRM creates database triggers. Make sure no users are logged in to the Siebel database before you create database triggers.
• You define a large number of database triggers because too many workflow policies exist.

Manually Running the SQL Script File

After Generate Triggers finishes, if the EXEC parameter is FALSE, then you must run the SQL script file.

To manually run the SQL script file

1. Connect to the server that contains the database. Connect as the Siebel table owner. Use the SQL tool that your RDBMS vendor provides.

   For example, use ISQL for Microsoft or SQL*Plus for Oracle.

2. Run the SQL script file that the Trigger File Name parameter references.

   The default file name is TRIGGER.SQL. The default location of this file is the root directory of where you installed the Siebel Server. For example:

   C:\siebsrvr\trigger.sql

   If your environment uses an MS SQL server database, then you must run the trigger.sql script as the owner login, which is also known as the dbo login.

3. Make sure Siebel CRM reports no errors.

   For example, assume you finish defining workflow policies in the Siebel client and must set the database triggers for these new policies. To set the file output name, you use Generate Triggers which creates a TRIGGER.SQL file that contains the database triggers that you modify or define in the test database. To create the database triggers in the Oracle database, you then run the following command in SQL*Plus:

   SQL>@path\mytrig.sql

   Oracle then indicates the successful creation of each database trigger. For information on the format that other databases require, see your database documentation.

Guidelines for Configuring Database Triggers

If you configure the Generate Triggers server component, then it is recommended that you use the following guidelines:

• If you delete a workflow policy, then you must run Generate Triggers with the remove parameter set to TRUE, which removes every database trigger. You must then rerun Generate Triggers to reset the database triggers for the remaining workflow policies.
• You must stop and restart the workflow monitor agents when you run Generate Triggers.
• If you change a workflow policy condition or a workflow policy group, then you must rerun Generate Triggers. It is not necessary for you to rerun Generate Triggers if you change a workflow policy action. For more information, see Moving a Workflow Policy to a Different Group.
• If you configuration uses a SQL Server, then make sure you set your default database correctly. To determine your default database, start the SQL Server Enterprise Manager, and then navigate to the SQL Server Machine name. Next, click Security, and then click LOGIN. The default database is listed to the right.
• If you drop or recreate database task triggers, then you must start a new Workflow Monitor Agent, which refreshes the cache for this agent.
• If a table name exceeds 18 characters, then Generate Triggers fails with an error that is similar to the following:

  18 character limit, table_name trigger fail

• If you run Generate Triggers, then the limit on table names that DB2 SQL uses results in limiting the database trigger name to 18 characters. Siebel CRM derives the database trigger name from the table name plus a suffix, such as U, I, D, U1, I1, D1, and so on.

Using Database Triggers with Remote Users

If a remote user synchronizes, then Siebel CRM incorporates changes in the Siebel database. For example, it updates account information in the S_ORG_EXT table. If you configure a workflow process that creates database triggers that compare changes in the Siebel database to a workflow policy condition, and if these changes affect this condition during synchronization, then the database triggers fire and Siebel CRM writes rows to the S_ESCL_REQ table. For more information, see Tables That Workflow Monitor Agent Uses.

Managing Database Triggers and Database Administration

It is important to make sure your database administrators are informed of database triggers that are active for a workflow process. A database update or insert event causes the database trigger to react, regardless of how the event runs. For example, if inserts to the S_SRV_REQ table exist, and if the Siebel database administrator performs a table export and import of these records, then the database triggers treat every record in the Siebel database as if it is a newly inserted record. This situation can result in Siebel CRM inappropriately modifying old records that were simply imported again.

Beginning with Siebel CRM version 8.1, the Generate Triggers server task requires the Privileged User Name and Password instead of Table Owner ID and Password.

Fixing Problems in the S_ESCL_REQ Table

If a database trigger runs on a workflow policy condition, then Siebel CRM inserts a record in the S_ESCL_REQ (escalation request) table. This table contains the rows that can cause a workflow policy to run. After the Workflow Monitor Agent processes a request, it removes the row from this table.

A database trigger does not include the logic that you define in a workflow policy condition. The conditions in the database trigger file might not be indicative of the workflow policies that are met. When Workflow Monitor Agent runs, the records in the S_ESCL_REQ table causes Siebel Workflow to evaluate the related workflow policy conditions. The database triggers exist only to trigger the Workflow Engine to examine the workflow policy conditions.

For more information about the S_ESCL_REQ table, see About the Workflow Monitor Agent.

To fix problems in the S_ESCL_REQ table

• Use your database tools to monitor the size and efficiency of the table:
  • If the table is very large, then this situation indicates that Siebel CRM is monitoring too many workflow policies. To fix this problem, redefine the workflow policies so that they share the load.
  • If Siebel CRM monitors rows but does not remove them after the time interval finishes, then this situation might indicate that you deactivated a workflow policy but did not remove the database triggers that are associated with this policy. The database triggers continue to send data that no workflow policy works on. To fix this problem, remove the database triggers that are associated with the deactivated workflow policy.
• If you create a new business component, then do not set the Table property to the S_ESCL_REQ table. A business component cannot reference 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.