Guest

Cisco Unified Intelligent Contact Management Enterprise

Using Database Triggers with Blended Agent to do Application-Specific Processing

Cisco - Using Database Triggers with Blended Agent to do Application-Specific Processing

Document ID: 19091

Updated: Jun 29, 2006

   Print

Introduction

This white paper describes how to use SQL Server database triggers to perform application-specific processing, when the contact records in the Blended Agent (BA) private database are closed or are updated by the BA Campaign Manager.

Prerequisites

Requirements

Cisco recommends that you have knowledge of these topics:

  • Cisco Intelligent Contact Management (ICM)

  • Cisco ICM Software Blended Agent

  • Microsoft SQL Database

Components Used

The information in this white paper is based on these software and hardware versions:

  • Cisco ICM version 4.6.x and later

  • Microsoft SQL 7

The information in this white paper was created from the devices in a specific lab environment. All of the devices used in this white paper started with a cleared (default) configuration. If your network is live, make sure that you understand the potential impact of any command.

Conventions

Refer to Cisco Technical Tips Conventions for more information on document conventions.

Overview

The BA stores the contact records to be dialed in a Microsoft SQL Server database. The use of a standard database application, such as SQL Server, makes it easier for third parties and users to integrate their systems and applications with the BA.

This white paper describes how to use SQL Server database triggers to perform application-specific processing, when the BA Campaign Manager closes or updates the status of any connect record in the database.

Dialing_List and Personal_Callback_List

The BA stores the contact records in the Dialing_List table. It stores the personal callback records in the Personal_Callback_List table. These tables are in the BA private database (customer_inst_baA). In this table, CallStatus and CallResult identify the state of a contact record.

Name Type Size Description
CallStatus CHAR 1 Byte CallStatus indicates the state of the record: P—The contact record is PENDING to be called. C—The record has been closed. Refer to these documents for a complete description of all CallStatus values and their meanings:
CallResult SMALLINT 2 Bytes CallResult indicates the result of the call as returned to the BA Campaign Manager by the BA dialer. Thus, it indicates whether the dialer displays VOICE, BUSY, or NO ANSWER when the contact number is dialed. Refer to these documents for complete descriptions of all CallResult values and their meanings:

Whenever the BA Campaign Manager updates a record in the Dialing_List or in the Personal_Callback_List, the CallStatus and CallResult fields are updated. For this reason, it is simple to create SQL Server database triggers to monitor these fields.

The use of triggers on the BA database tables allows you to accomplish an unlimited number of application-specific objectives. This white paper demonstrates some sample scenarios where triggers can be useful.

Sample Triggers

Note: All of the sample triggers in this white paper refer to a fictitious table, the CRM_Table in the CRM_DB database.

Complete these steps to create triggers from the SQL Server Enterprise Manager:

  1. Drill-down to the BA database.

  2. Under the database, click the Table icon.

    This displays the tables that are associated with the database.

  3. Select the table on which you want to create a trigger.

  4. Right-click the selected table and select All Tasks > Manage Triggers.

    blendedagent-a.gif

Insert a Row into an External Database

The sample trigger in this section inserts a row in the table, CRM_Table (in the CRM_DB database) whenever the BA Campaign Manager updates a contact record in the Dialing_List.

CREATE TRIGGER insert_crm_data
CREATE TRIGGER insert_crm_data ON Dialing_List FOR UPDATE AS
/* Proceed only if EXACTLY ONE Record is updated */

!--- See next Note for an explanation.

IF (SELECT COUNT (*) FROM INSERTED) = 1
BEGIN

     INSERT CRM_DB..CRM_Table
     SELECT
     inserted.AccountNumber,
     GETDATE (),
     inserted.CallStatus
     FROM inserted

END

Note: Other than update contact records when the contacts are called, the Campaign Manager also performs some bulk updates. These should be filtered out by the trigger. See the Recommended Practices section of this white paper for more information.

Update Contact Information in an External Database

The sample trigger in this section updates a record in the table CRM_Table (in the CRM_DB database) through a match of the account number of the contact. For example, if the BA Campaign Manager updates the record in Dialing_List with Account Number 1A2B3C4, then the record in CRM_Table with the same account number is updated simultaneously.

CREATE TRIGGER updt_customer_by_acctnum
CREATE TRIGGER updt_customer_by_acctnum ON Dialing_List FOR UPDATE AS
/* Proceed only if EXACTLY ONE Record is updated */
IF (SELECT COUNT (*) FROM INSERTED) = 1
BEGIN

     DECLARE @CS CHAR (1)
     DECLARE @ACCTNUM VARCHAR (25)
     SELECT
     @CS = inserted.CallStatus,
     @ACCTNUM = inserted.AccountNumber
     FROM inserted
     UPDATE Northwind..CRM_Table
     SET Status = @CS
     Where Northwind..CRM_Table.AccountNumber = @ACCTNUM

END

Delete a Row from an External Database

The sample trigger in this section deletes a record from the table CRM_Table (in the CRM_DB database) through a match of the account number of the contact, if that contact record has been closed in the Dialing_List. For example, if the BA Campaign Manager closes the record in Dialing_List with Account Number 1A2B3C4, then the record in CRM_Table with the same account number is deleted.

CREATE TRIGGER del_customer_by_acctnum
CREATE TRIGGER del_customer_by_acctnum ON Dialing_List FOR UPDATE AS
/* Proceed only if EXACTLY ONE Record is updated */
IF (SELECT COUNT (*) FROM INSERTED) = 1
BEGIN

     DECLARE @CS CHAR (1)
     DECLARE @ACCTNUM VARCHAR (25)
     SELECT
     @CS = inserted.CallStatus,
     @ACCTNUM = inserted.AccountNumber
     FROM inserted
     IF @CS = 'C'
     BEGIN
          DELETE Northwind..CRM_Table
          WHERE Northwind..CRM_Table.AccountNumber = @ACCTNUM
     END
END

Run an External Executable from a Trigger

The sample trigger in this section runs the program C:\MYAPP.EXE. The trigger passes the AccountNumber and CallStatus of the updated record as command line arguments to MYAPP.EXE. This occurs whenever the BA Campaign Manager updates the status of exactly one record.

CREATE TRIGGER run_executable
CREATE TRIGGER run_executable ON Dialing_List FOR UPDATE AS
/* Proceed only if EXACTLY ONE Record is updated */
IF (SELECT COUNT (*) FROM INSERTED) = 1
BEGIN
     DELCARE *CMD VARCHAR (100)
     SELECT
     @CMD = 'C:\MYAPP.EXE ' + inserted.AccountNumber + ' ' + inserted.CallStatus
     FROM inserted
     EXEC master ..xp_cmdshell @CMD
END

Note: In the preceding table, rather than call the xp_cmdshell stored procedure to start an executable, the trigger can also execute any other stored procedure.

Performance Considerations

  • SQL Server triggers are executed synchronously. This means if there is an update trigger on a table, an update command on that table does not return until the trigger execution is finished. If the triggers are not optimally designed, then the BA database updates are slowed. Also, the BA Campaign Manager might encounter open database connectivity (ODBC) timeouts while it is executing SQL statements to update the tables. As a rule, a trigger should do minimum possible processing and should be optimized for execution speed.

  • The result of the trigger execution might impact the result of original SQL operation. For example, assume that an update trigger is defined on Dialing_List. If the update trigger fails, then the update operation on Dialing_List might also fail and the record in question can be left in Dialing_List with an incorrect CallStatus.

  • If a trigger is nested, then the SQL operation does not complete until all nested triggers are run. Therefore, the failure of any trigger might fail the original SQL operation.

  • If a trigger executes an external application or another stored procedure, then the trigger execution is not complete until the external application or stored procedure is finished.

  • The use of cursors in triggers is not recommended, because of the potentially negative impact on performance.

  • The triggers should not perform CPU or memory-intensive tasks.

  • All of the triggers should be well tested and stressed before deployment.

Recommended Practices

  • Always verify that only one record is updated before you proceed. This prevents the trigger from acting on bulk updates done by the Campaign Manager. These are examples of bulk updates which are generally filtered out by the trigger:

    • A change in the CallStatus of a record from A (ACTIVE) to U (UNKNOWN), when a dialer with ACTIVE records disconnects from the Campaign Manager.

    • The Campaign Manager periodically updates records in U CallStatus to P (PENDING).

    Note: When a contact record is updated as a result of a call, the records are always updated one at a time. Therefore, the verification to see if exactly one record was updated safeguards against unwanted trigger actions most of the time. An exception to this case occurs when a bulk operation updates only one row.

  • Check for the CallStatus value before you proceed. For example, when the Campaign Manager sends a record to the BA dialer, the Campaign Manager sets the CallStatus to A (ACTIVE). A trigger can check for CallStatus. If Updated CallStatus is A, it does not proceed any further.

  • In order to optimize performance during BA database updates, time and resource-intensive operations must not be performed in the trigger. You can create an application-specific table in another database to serve as a queue. This speeds up trigger execution. You can use the trigger to insert, update, or delete data in that application-specific table. Then, another application can poll that table for changes and perform further tasks as needed.

Related Information

Updated: Jun 29, 2006
Document ID: 19091