Programmatically disable/enable active workflows

Overview

Being able to disable all active workflows after an upgrade, or for other reasons, allows you to perform some validation (smoke testing) before all active workflows are activated and executed based on their defined schedules. There is currently not a system-level mode to allow this to occur, so the process has been outlined in this page.

Programmatically disable/enable active workflows

These are the steps to disable/enable all active workflows. The intent is to pause active workflows while performing basic smoke testing within the environment. This is a step that can be performed any time, but in this example, it’s a smoke test after a product upgrade.

The expectation is that this is done after the system has been put into maintenance mode for enough time for running campaigns to complete.

All of these steps are performed in the PULSE Operations Database:

Deactivate active triggers:
1. Backup active triggers to a temp table.
2. Set active triggers to inactive.
3. Backup rpi_ExecutionSchedule and rpi_ExecutionPlans table.
4. Truncate rpi_ExecutionSchedule and rpi_ExecutionPlans table.
After you have performed smoke testing, then you can enable active workflows. To reactivate active triggers:
1. Restore rpi_ExecutionSchedule and rpi_ExecutionPlans table.
2. Reactivate previously active triggers based on backup table.
3. Clean up backup tables.

SQL statements:

SQL

Steps to Disable/Enable Active Triggers.  
** This has been tested in a local instance but not yet validated by R&D or QA.

The expectation is that this is done after the system has been put into maintenance mode for enough time for running campaigns to complete to achieve optimal results.
/* Steps (Need to be validated):
All of these steps are performed in the PULSE Operations Database
1. Backup active triggers to a temp table
2. Set active triggers to Inactive
3. Backup rpi_ExecutionSchedule and rpi_ExecutionPlans table
4. Truncate rpi_ExecutionSchedule and rpi_ExecutionPlans table
5. Restore rpi_ExecutionSchedule and rpi_ExecutionPlans table
6. Reactivate previously active triggers based on backup table
7. Cleanup backup tables.
*/

-- SQL to perform steps listed above:
-- Backup List of Active Triggers
Select * into [dbo].[op_InteractionTriggers_Backup] from [dbo].[op_InteractionTriggers]
WHERE IsActive = 1

-- Backup rpi_ExecutionSchedule and rpi_ExecutionPlans tables
Select * into [dbo].[rpi_ExecutionSchedule_Backup] from [dbo].[rpi_ExecutionSchedule]
Select * into [dbo].[rpi_ExecutionPlans_Backup] from [dbo].[rpi_ExecutionPlans]

-- Disable Active Trigers
UPDATE op_InteractionTriggers SET IsActive = 0, LastEvent = GETUTCDATE() WHERE IsActive = 1

-- Truncate execution tables
TRUNCATE TABLE [dbo].[rpi_ExecutionSchedule]
TRUNCATE TABLE [dbo].[rpi_ExecutionPlans]

/** After you have perfomed smoke testing then you can enable active workflows. **/
-- Reactivate Active Triggers--

-- Restore rpi_Execution tables
-- Restore [dbo].[rpi_ExecutionSchedule]
INSERT INTO [dbo].[rpi_ExecutionSchedule]
SELECT * FROM [dbo].[rpi_ExecutionSchedule_Backup]

-- Restore [dbo].[rpi_ExecutionPlans]
INSERT INTO [dbo].[rpi_ExecutionPlans]
SELECT * FROM [dbo].[rpi_ExecutionPlans_Backup]

-- Enable Active Triggers based on backup of active triggers op_InteractionTriggers_Active_Trigger_Backup
UPDATE op_InteractionTriggers SET IsActive = 1, LastEvent = GETUTCDATE()
WHERE
TriggerID in (Select TriggerID from [dbo].[op_InteractionTriggers_Backup] )

--Cleanup - this can be done immediately after the process or left for a time for review.
drop table [dbo].[op_InteractionTriggers_Backup];
drop table [dbo].[rpi_ExecutionSchedule_Backup];
drop table [dbo].[rpi_ExecutionPlans_Backup];