Document ID: 19091
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.
Cisco recommends that you have knowledge of these topics:
Cisco Intelligent Contact Management (ICM)
Cisco ICM Software Blended Agent
Microsoft SQL Database
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.
Refer to Cisco Technical Tips Conventions for more information on document conventions.
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.
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.
CallStatus indicates the state of the record:
P—The contact record is PENDING
to be called.
C—The record has been
Refer to these documents for a complete description of all
CallStatus values and their meanings:
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.
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:
Drill-down to the BA database.
Under the database, click the Table icon.
This displays the tables that are associated with the database.
Select the table on which you want to create a trigger.
Right-click the selected table and select All Tasks > Manage Triggers.
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.
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
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
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.
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.
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.
- Cisco ICM Software Blended Agent Setup and Configuration Guide
- Cisco ICM Software Blended Agent User Guide
- Technical Support & Documentation - Cisco Systems
|Updated: Jun 29, 2006||Document ID: 19091|