

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/App_CGAssocTriggerProcessing.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/App_CGAssocTriggerProcessing.sp,v $ $Id: App_CGAssocTriggerProcessing.sp,v 1.1.2.17.8.1 2021/06/10 04:27:05 mnatarajan Exp $";
-- Procedure Name
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='App_CGAssocTriggerProcessing')
BEGIN
	print '>>> Drop Stored Procedure: App_CGAssocTriggerProcessing <<<'
	drop procedure App_CGAssocTriggerProcessing
END
IF EXISTS (select * from GxQscripts where name='App_CGAssocTriggerProcessing')
	delete from GxQscripts where name = 'App_CGAssocTriggerProcessing'
GO

IF EXISTS (select * from GXDBVersions where aliasname='App_CGAssocTriggerProcessing')
	delete from GXDBVersions where aliasname = 'App_CGAssocTriggerProcessing'
GO
print '... Creating Procedure: App_CGAssocTriggerProcessing'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure App_CGAssocTriggerProcessing
  @clientID INTEGER
-- Input arguments
AS
-- Following are the "columns" returned, in the order in which they are declared
  DECLARE @o_errorCode int = 0
  DECLARE @o_errorCodeStr nvarchar(1024) = ''
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	SET NOCOUNT ON
	DECLARE @rowCnt INT = 0
	DECLARE @startTime INT = 0
	-- set to current time - 2
	-- Adding a 2 second delay to avoid race condition when rows are simultaneously inserted into the table
	-- We dont need to do this when clientId is passed. Assumption here is this is called after client client group refresh is done from scg update work token. This is done to ensure registration completes quickly.
	-- In case clientId passed in then use now time with no -2 second delay so that registration is not delayed.
	IF (@clientID=0)
	BEGIN
		SET @startTime = dbo.GetUnixTime(GetDate()) - 2 -- Since the one in TM_assocEntity uses Getdate unixtime
	END
	ELSE
	BEGIN
		SET @startTime = dbo.GetUnixTime(GetDate()) -- Since the one in TM_assocEntity uses Getdate unixtime
	END
	if object_id('tempdb.dbo.#AppClientCGAssocTrigger') IS NOT NULL
	BEGIN
		DROP TABLE #AppClientCGAssocTrigger
	END
	CREATE TABLE #AppClientCGAssocTrigger
	(
		id						integer,
		clientGroupId			integer,
		clientId				integer,
		createdTime				integer,
		opType					integer,	-- 2 is association added, 1 is association modified and 3 is association deleted.Corresponds to ListOperationType enum
		PRIMARY KEY(ID)
	)
	CREATE NONCLUSTERED INDEX AppClientCGAssocTrigger_clientId_clientGroupId_Idx ON #AppClientCGAssocTrigger(clientId, clientGroupId);;
	CREATE NONCLUSTERED INDEX AppClientCGAssocTrigger_createdTime_Idx ON #AppClientCGAssocTrigger(createdTime);;
	BEGIN TRY
		IF (@clientID>0)
		BEGIN
			INSERT INTO #AppClientCGAssocTrigger(id,clientGroupId,clientId,createdTime,opType )
			SELECT id,clientGroupId,clientId,createdTime,opType FROM APP_ClientGroupAssocTrigger (NOLOCK)
			WHERE createdTime<=@startTime AND ClientId = @clientID
		END
		ELSE
		BEGIN
			INSERT INTO #AppClientCGAssocTrigger(id,clientGroupId,clientId,createdTime,opType )
			SELECT id,clientGroupId,clientId,createdTime,opType FROM APP_ClientGroupAssocTrigger (NOLOCK)
			WHERE createdTime<=@startTime
		END
		IF NOT EXISTS(SELECT 1 FROM #AppClientCGAssocTrigger)
		BEGIN
			SELECT @o_errorCode, @o_errorCodeStr
			RETURN
		END
		DECLARE @insertCount INT = (SELECT COUNT(1) FROM #AppClientCGAssocTrigger (NOLOCK) WHERE opType=2 ) -- Inserted entries
		DECLARE @deleteCount INT = (SELECT COUNT(1) FROM #AppClientCGAssocTrigger (NOLOCK) WHERE opType=3 ) -- Deleted entries
		DECLARE	@currentTime INT = dbo.GetUnixTime(GetUTCDate())
		DECLARE @pushFWClientList TABLE (clientId INT)
		DECLARE @isTranStarted INT = 0
		IF object_id('tempdb.dbo.#ClientTable') IS NOT NULL
		BEGIN
			DROP TABLE #ClientTable
		END
		IF object_id('tempdb.dbo.#ClientListTable') IS NOT NULL
		BEGIN
			DROP TABLE #ClientListTable
		END
		CREATE TABLE #ClientListTable (clientId INT, cgId INT, isExternal INT)
		CREATE TABLE #ClientTable (clientId INT, clientGroupId INT, timeZone NVARCHAR(1024) DEFAULT NULL)
		CREATE CLUSTERED INDEX ClientTable_clientId_Index ON #ClientTable (clientId)
		CREATE INDEX ClientTable_clientGroupId_Index ON #ClientTable (clientGroupId)
		IF OBJECT_ID('tempdb.dbo.#WorkTokensForWQ') IS NOT NULL
			DROP TABLE #WorkTokensForWQ
		CREATE TABLE #WorkTokensForWQ (
			clientId		INT,
			workToken 		INT,
			workTokenParams		NVARCHAR(MAX),
			flags			INT DEFAULT 1
		)
		CREATE INDEX WorkTokensForWQ_idx1 on #WorkTokensForWQ (clientId, workToken)
		DECLARE @external INT = 0
		DECLARE @sParam XML
		IF OBJECT_ID('tempdb..#smartTopoPushList') IS NOT NULL
			DROP TABLE #smartTopoPushList
		CREATE TABLE #smartTopoPushList
		(
			clientId INT
		)
		IF OBJECT_ID('tempdb..#clientsInSmartTopoCG') IS NOT NULL
			DROP TABLE #clientsInSmartTopoCG
		CREATE TABLE #clientsInSmartTopoCG
		(
			clientId INT,
			forCSTopoMnemonic INT,
			forMATopoMnemonic INT
		)
		IF OBJECT_ID('tempdb..#proxyGroupsOfSmartTopo') IS NOT NULL
			DROP TABLE #proxyGroupsOfSmartTopo
		CREATE TABLE #proxyGroupsOfSmartTopo
		(
			clientGroupId INT PRIMARY KEY
		)
		IF OBJECT_ID('tempdb..#cgAssocAddedOrDeleted') IS NOT NULL
			DROP TABLE #cgAssocAddedOrDeleted
		CREATE TABLE #cgAssocAddedOrDeleted
		(
			clientId INT,
			clientGroupId INT,
			PRIMARY KEY(clientId, clientGroupId)
		)
		IF (@insertCount > 0 OR @deleteCount > 0)
		BEGIN
			INSERT INTO #cgAssocAddedOrDeleted (clientId, clientGroupId)
			SELECT clientId, clientGroupId FROM #AppClientCGAssocTrigger (NOLOCK) WHERE opType=2 AND createdTime<=@startTime -- Inserted entries
			UNION
			SELECT clientId, clientGroupId FROM #AppClientCGAssocTrigger (NOLOCK) WHERE opType=3 AND createdTime<=@startTime -- Deleted entries
			INSERT INTO #proxyGroupsOfSmartTopo
			SELECT DISTINCT AFTA2.groupId FROM #cgAssocAddedOrDeleted CGAssoc
			INNER JOIN APP_FirewallTopologyAssoc AFTA1 WITH (NOLOCK) ON CGAssoc.clientGroupId = AFTA1.groupId AND AFTA1.fwGroupType IN (1,2,3)
			INNER JOIN APP_FirewallTopology AFT WITH (NOLOCK) ON AFTA1.topologyId = AFT.topologyId AND AFT.flag & 1 = 1
			INNER JOIN APP_FirewallTopologyAssoc AFTA2 WITH (NOLOCK) ON AFTA1.topologyId = AFTA2.topologyId AND AFTA2.fwGroupType = 3
			-- Add the clients if the groups are present in smart topologies
			INSERT INTO #clientsInSmartTopoCG
			SELECT CGAssoc.clientId, CASE WHEN AFTA1.groupId IN (-1,-3) THEN 1 ELSE 0 END forCSTopoMnemonic, CASE WHEN AFTA1.groupId IN (-2,-3) THEN 1 ELSE 0 END forMATopoMnemonic FROM #cgAssocAddedOrDeleted CGAssoc
			INNER JOIN APP_FirewallTopologyAssoc AFTA WITH (NOLOCK) ON AFTA.groupId = CGAssoc.clientGroupId AND AFTA.fwGroupType IN (1,2) AND AFTA.groupId > 0
			INNER JOIN APP_FirewallTopologyAssoc AFTA1 WITH (NOLOCK) ON AFTA.topologyId = AFTA1.topologyId AND AFTA.fwGroupType IN (1,2) AND AFTA1.groupId < 0
			INNER JOIN APP_FirewallTopology AFT WITH (NOLOCK) ON AFT.topologyId = AFTA.topologyId AND AFT.flag & 1 = 1
			UNION
			-- Add all the clients in the non-mnemonic group if a client association of a proxy group is being changed. Push to the related MAs also will happen.
			SELECT ACGA.clientId, CASE WHEN AFTA3.groupId IN (-1,-3) THEN 1 ELSE 0 END forCSTopoMnemonic, CASE WHEN AFTA3.groupId IN (-2,-3) THEN 1 ELSE 0 END forMATopoMnemonic FROM #cgAssocAddedOrDeleted CGAssoc
			INNER JOIN APP_FirewallTopologyAssoc AFTA1 WITH (NOLOCK) ON CGAssoc.clientGroupId = AFTA1.groupId AND AFTA1.fwGroupType = 3
			INNER JOIN APP_FirewallTopology AFT WITH (NOLOCK) ON AFTA1.topologyId = AFT.topologyId AND AFT.flag & 1 = 1
			INNER JOIN APP_FirewallTopologyAssoc AFTA2 WITH (NOLOCK) ON AFTA1.topologyId = AFTA2.topologyId AND AFTA2.fwGroupType IN (1,2) AND AFTA2.groupId > 0
			INNER JOIN APP_FirewallTopologyAssoc AFTA3 WITH (NOLOCK) ON AFTA3.topologyId = AFTA1.topologyId AND AFTA3.fwGroupType IN (1,2) AND AFTA3.groupId < 0
			INNER JOIN APP_ClientGroupAssoc ACGA WITH (NOLOCK) ON AFTA2.groupId = ACGA.clientGroupId
			IF EXISTS(SELECT 1 FROM #clientsInSmartTopoCG)
			BEGIN
				IF OBJECT_ID('tempdb..#inputClientList') IS NOT NULL
					DROP TABLE #inputClientList
				CREATE TABLE #inputClientList
				(
					clientId INT PRIMARY KEY
				)
				INSERT INTO #inputClientList
				SELECT DISTINCT clientId FROM #clientsInSmartTopoCG WHERE forMATopoMnemonic = 1
--Code Below Here is From AppGetClientMediaAgentMap.spb
BEGIN
	IF OBJECT_ID('tempdb.dbo.#ClientMediaAgentMap') IS NOT NULL
		DROP TABLE #ClientMediaAgentMap
	CREATE TABLE #ClientMediaAgentMap
	(
		clientId INT,
		mediaAgentId INT,
		PRIMARY KEY(clientId,mediaAgentId)
	);
	IF OBJECT_ID('tempdb..#lt_Clients') IS NOT NULL DROP TABLE #lt_Clients
	CREATE TABLE #lt_Clients
	(
		clientId	INT
		PRIMARY KEY(clientId)
	)
	IF OBJECT_ID('tempdb.dbo.#tempArchGrp') IS NOT NULL
		DROP TABLE #tempArchGrp
	CREATE TABLE #tempArchGrp
	(
		clientId	INT,
		archGroupId	INT,
		primaryCopyId smallint,
		primarySnapCopyId smallint
	)
	CREATE CLUSTERED INDEX  tempArchGrp_idx1 ON #tempArchGrp(primaryCopyId)
	CREATE NONCLUSTERED INDEX  tempArchGrp_idx2 ON #tempArchGrp(primarySnapCopyId, clientId)
DECLARE @cs_assocPlan INT = checksum('Associated Plan')
	-- Get all index servers
	IF OBJECT_ID('tempdb.dbo.#getIndexServers') IS NOT NULL     DROP TABLE #getIndexServers
	CREATE TABLE #getIndexServers
						(     serverURL NVARCHAR(512),
								clientId INT,
								clientName NVARCHAR(256),
								cloudId INT,
								hostName NVARCHAR(256),
								basePort INT,
								engineName  NVARCHAR(256),
								serverType INT,
								indexServerClientId INT,
								internalCloudName NVARCHAR(256)
						)
	EXEC DM2GetIndexServers
	-- Get Source Clients
		INSERT 	INTO #lt_Clients
		SELECT	CN.id
		FROM	App_Client CN WITH(READUNCOMMITTED)
				,#inputClientList CL
		WHERE	CN.id > 1
				AND CL.clientId = CN.id
	--
	--Find destination clients
	--
	IF @@ROWCOUNT > 0
	BEGIN
		-- Get all the storage policies
		INSERT INTO #tempArchGrp
		SELECT 	distinct APP.clientId, AG.id, AG.defaultCopy, AG.defaultSnapCopy
		FROM	#lt_Clients CN
				INNER JOIN App_Application APP WITH(READUNCOMMITTED)
				ON CN.clientId = APP.clientId
				INNER JOIN 	archGroup AAG WITH(READUNCOMMITTED)
				ON APP.dataArchGrpId = AAG.id,
				archGroup AG
		WHERE 	AG.id in (AAG.id, AAG.incrSP)
				AND AG.id > 1
		UNION
		SELECT 	distinct APP.clientId, AG.id, AG.defaultCopy, AG.defaultSnapCopy
		FROM	#lt_Clients CN
				INNER JOIN App_Application APP WITH(READUNCOMMITTED)
				ON CN.clientId = APP.clientId
				INNER JOIN 	archGroup AAG WITH(READUNCOMMITTED)
				ON APP.logArchGrpId = AAG.id,
				archGroup AG
		WHERE 	AG.id in (AAG.id, AAG.incrSP)
				AND AG.id > 1
		--Get client to infrastructure map
		INSERT INTO #ClientMediaAgentMap
		-- MediaAgents
		SELECT 	TA.clientId, DPool.clientId
		FROM 	#tempArchGrp TA,
				MMDataPath DPath WITH(READUNCOMMITTED),
				MMDrivePool DPool WITH(READUNCOMMITTED)
		WHERE	TA.primarySnapCopyId = DPath.copyId
				AND DPath.copyId > 0
				AND DPath.DrivePoolId = DPool.DrivePoolId
		UNION
		SELECT 	TA.clientId, DPool.clientId
		FROM 	#tempArchGrp TA,
				MMDataPath DPath WITH(READUNCOMMITTED),
				MMDrivePool DPool WITH(READUNCOMMITTED)
		WHERE	TA.primaryCopyId = DPath.copyId
				AND DPath.copyId > 0
				AND DPath.DrivePoolId = DPool.DrivePoolId
		UNION
		-- DDB Engines
		SELECT 	TA.clientId, SS.clientId
		FROM 	#tempArchGrp TA,
				archCopySIDBStore CPS WITH(READUNCOMMITTED),
				IdxSIDBStore S WITH(READUNCOMMITTED),
				IdxSIDBSubStore SS WITH(READUNCOMMITTED)
		WHERE	TA.primaryCopyId = CPS.copyId
				AND CPS.SIDBStoreId = S.SIDBStoreId
				AND S.sealedTime = 0 --ActiveStores
				AND S.SIDBStoreId = SS.SIDBStoreId
		UNION
		-- IndexServers: Exchange
		SELECT 	CN.clientId, S.clientId
		FROM 	#lt_Clients CN,
				APP_IDAName IDN WITH(READUNCOMMITTED),
				App_IdaProp IP WITH(READUNCOMMITTED), #getIndexServers S
		WHERE  	CN.clientId = IDN.clientId
				AND IP.componentNameId = IDN.ID
				AND IP.attrName = 'OnePass Index Server'
				AND IP.modified = 0
				AND S.indexServerClientId = CAST (IP.AttrVal AS INT)
		UNION
		-- IndexServers: Edge
		SELECT  CN.clientId, S.clientId
		FROM  	#lt_Clients CN,
				App_EdgeDriveAssociation EDGE WITH(READUNCOMMITTED), #getIndexServers S
		WHERE  	CN.clientId = EDGE.clientId
AND S.indexServerClientId = EDGE.entityId AND EDGE.entityType = 3
		UNION
		-- IndexServers: Edge Laptop clients
		SELECT	CN.clientId, EDW.webserverId
		FROM 	#lt_Clients CN
				INNER JOIN App_Client CL  WITH(READUNCOMMITTED) ON CN.clientId = CL.id
				INNER JOIN App_SyncCloudConfig SC  WITH(READUNCOMMITTED) ON CL.id = SC.clientId
				INNER JOIN App_SyncCloudFolder SF  WITH(READUNCOMMITTED) ON SC.syncWebFolderId = SF.syncWebFolderId
				INNER JOIN APP_Application APP  WITH(READUNCOMMITTED) ON APP.id = SC.subclientId
				INNER JOIN AppEdgeDriveWebserverAssociation EDW  WITH(READUNCOMMITTED) ON EDW.edgeClientId = APP.clientId
WHERE 	CL.status & 0x1000 > 0
AND SF.flag &  0x10 > 0
AND APP.subclientStatus & 0x20000 > 0
		UNION
		-- IndexServers: FileSystem
		SELECT  CN.clientId, S.clientId
		FROM  	#lt_Clients CN,
				App_Application APP WITH(READUNCOMMITTED),
				App_IndexDBInfo idx WITH(READUNCOMMITTED), #getIndexServers S
		WHERE	CN.clientId = APP.clientId
				AND idx.backupSetId = APP.backupSet
				AND S.indexServerClientId = idx.currentIdxServer
		UNION
		-- IndexServers: Plan VSA
		SELECT  T.clientId, S.clientId
		FROM  	(
					SELECT 	CN.clientId, CAST(CP.attrVal AS INT) PlanId
					FROM 	#lt_Clients CN,
							App_clientProp CP WITH(READUNCOMMITTED)
					WHERE 	CN.clientId = CP.componentNameId
AND CP.attrName = 'Associated Plan'
							AND CP.modified = 0
					UNION
					SELECT 	CN.clientId, CAST(SCP.attrVal AS INT) PlanId
					FROM 	#lt_Clients CN,
							App_Application SC WITH(READUNCOMMITTED),
							App_SubclientProp SCP WITH(READUNCOMMITTED)
					WHERE 	CN.clientId = SC.clientId
							AND SCP.componentNameId = SC.id
							AND SCP.cs_attrName = @cs_assocPlan
AND SCP.attrName = 'Associated Plan'
							AND SCP.modified = 0
				) AS T,
				App_PlanProp PROP WITH(READUNCOMMITTED), #getIndexServers S
		WHERE	PROP.componentNameId = T.PlanId
				AND PROP.attrName = 'Analytics Index Server Id'
				AND PROP.modified = 0
				AND S.indexServerClientId = CAST (PROP.AttrVal AS INT)
		UNION
		-- Remote SoftwareCache: Client Level
		SELECT  CN.clientId, UA.clientId
		FROM  	#lt_Clients CN,
				App_clientProp CP WITH(READUNCOMMITTED),
				PatchUpdateAgentInfo UA WITH(READUNCOMMITTED)
		WHERE 	CN.clientId = CP.componentNameId
				AND CP.attrName = N'UPDATE CACHE AGENT ID'
				AND CP.modified = 0
				AND CAST(CP.attrVal AS INT) = UA.id
		UNION
		-- Remote SoftwareCache: ClientGroup Level
		SELECT  CN.clientId, UA.clientId
		FROM  	#lt_Clients CN,
				App_ClientGroupAssoc CGA WITH(READUNCOMMITTED),
				APP_ComponentProp CP WITH(READUNCOMMITTED),
				PatchUpdateAgentInfo UA WITH(READUNCOMMITTED)
		WHERE 	CN.clientId = CGA.clientId
				AND CGA.clientGroupId = CP.componentId
AND CP.componentType = 8
AND CP.propertyTypeId = 1300
				AND CP.modified = 0
				AND CP.longVal = UA.id
		--
		--Remove entries if source and dest are same
		--
		DELETE 	#ClientMediaAgentMap
		WHERE	clientId = mediaAgentId
		-- Remove entries for invalid MAs.
		-- Example: On deleting a DDB MA without deleting relevant partition info (feature introduced in SP19), a MA entry with clientid=1 (NOCLIENT) is used as a placeholder.
		-- Such MA ids are invalid for general processing
		DELETE 	#ClientMediaAgentMap
		WHERE	mediaAgentId < 2
	END
	IF OBJECT_ID('tempdb..#lt_Clients') IS NOT NULL 			DROP TABLE #lt_Clients
	IF OBJECT_ID('tempdb.dbo.#tempArchGrp') IS NOT NULL 		DROP TABLE #tempArchGrp
	IF OBJECT_ID('tempdb.dbo.#getIndexServers') IS NOT NULL     DROP TABLE #getIndexServers
END
				IF OBJECT_ID('tempdb..#inputClientList') IS NOT NULL
					DROP TABLE #inputClientList
				-- Push to clients in @clientsInSmartTopoCG
				INSERT INTO #smartTopoPushList
				SELECT clientId FROM #clientsInSmartTopoCG
				UNION
				-- Push to the client's media agents also
				SELECT CMA.mediaAgentId FROM #clientsInSmartTopoCG CST INNER JOIN #ClientMediaAgentMap CMA
				ON CST.clientId = CMA.clientId
				IF EXISTS(SELECT 1 FROM #clientsInSmartTopoCG WHERE forCSTopoMnemonic = 1)
				BEGIN
					--If there is atleast one client that is part of a smart topology with 'My Commserve' or 'My Commserve & Media Agents', push to CS.
					INSERT INTO #smartTopoPushList
					SELECT 2
				END
			END
			IF EXISTS(SELECT 1 FROM #proxyGroupsOfSmartTopo)
			BEGIN
				-- Push to all the proxies of the smart topologies also
				INSERT INTO #smartTopoPushList
				SELECT clientId FROM APP_ClientGroupAssoc ACGA WITH (NOLOCK)
				INNER JOIN #proxyGroupsOfSmartTopo ProxyGroups ON ACGA.clientGroupId = ProxyGroups.clientGroupId
			END
			IF EXISTS(SELECT 1 FROM #smartTopoPushList)
			BEGIN
				INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT clientId, 5 /* WORK_TOKEN_NETWORK */, '' FROM #smartTopoPushList
			END
			IF OBJECT_ID('tempdb..#smartTopoPushList') IS NOT NULL
				DROP TABLE #smartTopoPushList
			IF OBJECT_ID('tempdb..#clientsInSmartTopoCG') IS NOT NULL
				DROP TABLE #clientsInSmartTopoCG
			IF OBJECT_ID('tempdb..#proxyGroupsOfSmartTopo') IS NOT NULL
				DROP TABLE #proxyGroupsOfSmartTopo
			IF OBJECT_ID('tempdb..#cgAssocAddedOrDeleted') IS NOT NULL
				DROP TABLE #cgAssocAddedOrDeleted
		END
		-- Insert workqueue to update subclient policy associations if client group is associated to subclient policy
		IF @insertCount > 0 OR @deleteCount > 0
		BEGIN
			SET @sParam = (SELECT
								(SELECT 1 as '@isAdded',
									(SELECT CGAssoc.clientGroupId AS '@clientGroupId' FOR XML PATH('clientGroup'), TYPE),
									(SELECT clientId AS '@clientId'
										FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc1
											WHERE CGAssoc1.clientGroupId = CGAssoc.clientGroupId AND CGAssoc1.opType=2 -- Inserted entries
											AND CGAssoc1.createdTime<=@startTime
									FOR XML PATH('clients'), TYPE)
									FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc
									WHERE CGAssoc.opType=2 -- Inserted entries.
									AND CGAssoc.createdTime<=@startTime
									GROUP BY clientGroupId
								FOR XML PATH('addedOrRemovedCGAssoc'), TYPE),
								(SELECT 0 as '@isAdded',
									(SELECT CGAssoc.clientGroupId AS '@clientGroupId' FOR XML PATH('clientGroup'), TYPE),
									(SELECT clientId AS '@clientId'
										FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc1
											WHERE CGAssoc1.clientGroupId = CGAssoc.clientGroupId AND CGAssoc1.opType=3 -- Deleted entries
											AND CGAssoc1.createdTime<=@startTime
									FOR XML PATH('clients'), TYPE)
									FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc
									WHERE CGAssoc.opType=3 -- Deleted entries.
									AND CGAssoc.createdTime<=@startTime
									GROUP BY clientGroupId
								FOR XML PATH('addedOrRemovedCGAssoc'), TYPE)
							FOR XML PATH('App_SCPClientGroupAssocWorkQueueParam'))
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT 2, 42 /* WORK_TOKEN_UPDATE_SCP_ASSOCIATIONS */, ''
			INSERT INTO #ClientTable(clientId, clientGroupId)
				SELECT DISTINCT clientId, clientGroupId FROM #AppClientCGAssocTrigger (NOLOCK) WHERE createdTime<=@startTime -- Added entries
			-- check if any VM in request then submit request to all Physical nodes
			IF EXISTS( SELECT VMClientId FROM APP_VMToPMMap WITH(NOLOCK) WHERE VMClientId IN (SELECT clientId FROM #ClientTable WHERE clientId <> 2))
			BEGIN
				INSERT INTO #ClientTable (clientId, clientGroupId)
				SELECT DISTINCT PMClientId,ClientTable.clientGroupId FROM APP_VMToPMMap WITH(NOLOCK)
				INNER JOIN #ClientTable ClientTable ON ClientTable.clientId = VMClientId AND ClientTable.clientId<>2
				DELETE #ClientTable WHERE clientId IN (SELECT VMClientId FROM APP_VMToPMMap WITH(NOLOCK) WHERE VMClientId <> 2)
			END
			-- Send workqueue requests for registry update only when
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT DISTINCT ClientTable.clientId, 6, '' --WORK_TOKEN_REGISTRY
				FROM #ClientTable  ClientTable
				INNER JOIN App_AdvanceSettings (NOLOCK) ON
				ClientTable.ClientGroupId = App_AdvanceSettings.entityID
				AND App_AdvanceSettings.entityType=28 -- client group entity
				AND App_AdvanceSettings.sourceID = 0 -- Additional setting defined at CG level
				AND App_AdvanceSettings.sourceEntityType = 0 -- Additional setting defined at CG level
			-- Not sending WORK_TOKEN_CONFIG token here since some customers may not have osc schedule. So Sending only registry update. WORK_TOKEN_REGISTRY
			-- We need to insert token for OSC if client group is associated to OSC schedule or policy
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
			SELECT DISTINCT ClientTable.clientId, 4, '' -- OSC token in case client group has osc schedule association
			FROM    TM_Task TA WITH (NOLOCK)
			LEFT OUTER JOIN TM_AssocEntity TE WITH (NOLOCK) ON TA.taskId = TE.taskId
			INNER JOIN TM_SubTask SA WITH (NOLOCK) ON SA.taskId = TA.taskId
			INNER JOIN TM_PatternAssoc PA WITH (NOLOCK) ON PA.subTaskId = SA.subTaskId
INNER JOIN TM_Pattern P WITH (NOLOCK) ON P.patternId = PA.patternId AND P.freq_type=1024 -- FREQ_TYPE_Automatic_Schedule = 1024
LEFT JOIN TM_SubTaskOptions BL (NOLOCK) ON BL.subTaskId = SA.subTaskId AND BL.optionId = 458405394 --_BACKUPOPTION_BACKUP_LEVEL = 458405394
LEFT OUTER JOIN TM_SubTaskOptions SO WITH (NOLOCK) ON SO.subTaskId = SA.subTaskId AND SO.optionId = 239177580
			INNER JOIN #ClientTable ClientTable
			ON
			(
				(	--UDATE SCHEDULES ONLY
SA.subTaskType = 1 AND SA.operationType=4020 AND
					(
						(
							CAST(SO.value AS XML).exist('/clientAndClientGroups/@clientGroupId')=1 AND CAST(SO.value AS XML).value('(/clientAndClientGroups/@clientGroupId)[1]', 'INT') = ClientTable.clientGroupID AND -- Check for association at client group level
							( ISNULL( CAST(SO.value AS XML).value('(/clientAndClientGroups/@clientId)[1]', 'INT'),0) = 0 ) AND -- check for exclude flag below.
							ISNULL(CAST(SO.value AS XML).value('(/clientAndClientGroups/flags/@exclude)[1]', 'INT'),0) = 0
						)
						OR CAST(SO.value AS XML).exist('/clientAndClientGroups/@_type_[.="27"]')=1 -- ITs associated to all client grops entity ALL_CLIENT_GROUPS_ENTITY
					)
				)
				OR	--BACKUP SCHEDULES
				(
SA.subTaskType = 2 AND TE.clientGroupId = ClientTable.clientGroupId AND TE.clientId = 0 AND TE.exclude = 0 -- schedule/policy is associated at client group level directly and not at client level.
					AND (BL.value IS NULL OR  BL.value <> 4) -- _BACKUP_LEVEL_SYNTHETIC_FULL. Dont consider automatic synthetic full schedules for figuring out if we need to send OSC token
				)
			)
			AND TA.[deleted] = 0
			AND TA.[invalid] = 0
			AND TA.[uninstalled] = 0
			AND TA.[savedReport] = 0
			AND TA.[disabled] = 0
AND TA.[taskType] IN ( 2, 4 )
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT DISTINCT
					i.clientId,
					8,
					'<?xml version="1.0" encoding="UTF-8" standalone="no" ?><TMMsg_MonitoringPolicyChangeReq monitoringPolicyId="0" operationType="12"/>'
				FROM #ClientTable i
					INNER JOIN APP_MonitorAssocEntity (NOLOCK) mae
						ON mae.clientGroupId = i.clientGroupId -- Both Inserted and deleted entries.
					INNER JOIN APP_Client (NOLOCK) client
						ON client.id = i.clientId
				WHERE client.releaseId > 15	/* 15 = SIM_ID_GX_REL100 */
			-- Start workqueue(WORK_TOKEN_DLP_PUSH_CONFIG) to push DLP properties on clients removed from client group.
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT DISTINCT clientId, 11, ''
				FROM #AppClientCGAssocTrigger (NOLOCK) i
				INNER JOIN DlpGroupProp (NOLOCK)  prop ON prop.groupId = i.clientGroupId  -- Both Inserted and deleted clients/client group entries.
				AND i.createdTime<=@startTime
				WHERE prop.attrName IN(N'enableDLP', N'enableRmDLP') AND prop.attrVal = N'1' AND prop.modified = 0
			--Recompute runtimes for client timezone schedules for associated policies only for which timezone runtimes are not calculated
			UPDATE CT
			SET CT.timeZone =
			CASE
                WHEN tz.TimeZoneId IS NOT NULL THEN tz.TimeZoneStdName
                WHEN P.componentNameId IS NOT NULL THEN RIGHT(P.attrVal, CHARINDEX(':', REVERSE(P.attrVal)) - 1)
            END
			FROM #ClientTable CT
			LEFT OUTER JOIN APP_ClientProp P ON P.componentNameId = CT.clientId AND P.attrName = N'timezone'AND P.modified = 0
			LEFT OUTER JOIN APP_ClientProp P2 ON P2.componentNameId = CT.clientId AND P2.attrName = N'timezone id'AND P2.modified = 0
			LEFT OUTER JOIN SchedTimeZone tz ON P2.attrVal IS NOT NULL AND P2.attrVal = CAST(tz.TimeZoneID AS VARCHAR(12))
			DELETE TM_RunTime
			FROM TM_RunTime
				 INNER JOIN TM_Pattern(NOLOCK) ON TM_Pattern.patternId = TM_Runtime.patternId
					AND TM_pattern.Deleted = 0
					AND TM_pattern.tzId = 1001 --Client timezone
					AND TM_Runtime.Processed = 0
				 INNER JOIN TM_PatternAssoc(NOLOCK) ON TM_PatternAssoc.patternId = TM_Pattern.patternId
				 INNER JOIN TM_SubTask(NOLOCK) ON TM_SubTask.subTaskID = TM_PatternAssoc.subTaskId
AND TM_SubTask.subTaskType = 2
				 INNER JOIN TM_Task(NOLOCK) ON TM_Task.taskId = TM_SubTask.taskId
				    AND TM_Task.deleted = 0
AND TM_Task.taskType IN ( 2, 4 )
				 LEFT JOIN TM_AssocEntity(NOLOCK) ON TM_AssocEntity.taskId = TM_Task.TaskId
				 INNER JOIN #ClientTable ClientTable ON TM_AssocEntity.clientGroupId = ClientTable.clientGroupId
					AND TM_AssocEntity.clientId = 0
					AND TM_AssocEntity.exclude = 0
				 LEFT JOIN TM_RunTimeAssoc (NOLOCK) ON TM_RunTimeAssoc.runTimeId = TM_RunTime.runTimeId
					AND ClientTable.timeZone = TM_RunTimeAssoc.timeZoneNames
				 WHERE TM_RunTimeAssoc.runTimeAssocId IS NULL
					AND ClientTable.timeZone IS NOT NULL
			-- UPDATE schedule policy association to remove client group when client is no longer associated to CG
			UPDATE TM_AssocEntity
			SET clientGroupID = 0
			FROM TM_AssocEntity INNER JOIN #AppClientCGAssocTrigger AppClientCGAssocTrigger
			ON AppClientCGAssocTrigger.clientID = TM_AssocEntity.clientId
			AND AppClientCGAssocTrigger.clientGroupId = TM_AssocEntity.clientGroupId
			AND AppClientCGAssocTrigger.opType = 3 -- Client client group association is removed.
			AND TM_AssocEntity.clientId> 0 -- So if client is added from within CG node or excluded from within CG node, we retain inclusion/exclusion appropriately.
			LEFT JOIN App_ClientGroupAssoc WITH (NOLOCK) ON
			App_ClientGroupAssoc.clientId = TM_assocEntity.clientId AND
			App_ClientGroupAssoc.clientGroupId = TM_assocEntity.clientGroupId
			WHERE App_ClientGroupAssoc.clientId IS NULL -- DO it only when association is actually removed. Sometimes associations are removed and added
			IF ( CURSOR_STATUS('global', 'fwRefreshCursor') >= 0   OR CURSOR_STATUS('local', 'fwRefreshCursor') >= 0 )  -- CLOSE CURSOR IF OPEN
				CLOSE fwRefreshCursor
			IF ( CURSOR_STATUS('global','fwRefreshCursor') = -1     OR CURSOR_STATUS('local','fwRefreshCursor') = -1 )
				DEALLOCATE fwRefreshCursor
			DECLARE @externalCGID INT = 0
			DECLARE @internalCGID INT = 0
			DECLARE @ispreProcessingApplicable INT =0
			DECLARE fwRefreshCursor CURSOR FOR
				SELECT DISTINCT APP_ClientGroupAssocTrigger.clientGroupId,
					ISNULL( (SELECT TOP 1 ExternalGroupAssoc.groupId FROM
						APP_FirewallTopologyAssoc (NOLOCK) ExternalGroupAssoc
						INNER JOIN APP_FirewallTopology (NOLOCK) AS fwt
						ON ExternalGroupAssoc.topologyId = fwt.topologyId AND fwt.topologyType = 1 AND (fwt.flag&1) <> 1
						AND ExternalGroupAssoc.fwGroupType = 2
						INNER JOIN APP_FirewallTopologyAssoc (NOLOCK) InternalGroupAssoc
						ON  InternalGroupAssoc.fwGroupType = 1 AND InternalGroupAssoc.topologyId = fwt.topologyId AND InternalGroupAssoc.groupId = APP_ClientGroupAssocTrigger.clientGroupId
					),0)
				FROM #ClientTable (NOLOCK) APP_ClientGroupAssocTrigger
			OPEN fwRefreshCursor
			FETCH NEXT FROM fwRefreshCursor INTO @internalCGID, @externalCGID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				DELETE FROM @pushFWClientList
				INSERT INTO @pushFWClientList
				EXEC AppGetFWPushClientList	@internalCGID
				IF EXISTS (SELECT 1 FROM @pushFWClientList)
				BEGIN
					INSERT INTO #ClientListTable
					SELECT DISTINCT clientId, @internalCGID, 0 FROM #ClientTable (NOLOCK) WHERE clientGroupId = @internalCGID
					UNION
					SELECT clientId, @externalCGID, 1 FROM @pushFWClientList
				END
				FETCH NEXT FROM fwRefreshCursor INTO @internalCGID, @externalCGID
			END
			SET @ispreProcessingApplicable=0
			IF EXISTS (SELECT 1 FROM #ClientListTable WHERE isExternal=1)
			BEGIN
				-- IF Client id is persent and Firewall routes need to be pushed to CS also, then for client make sure we push to CS first
IF (@clientID > 0) AND EXISTS (SELECT 1 FROM #ClientListTable FW INNER JOIN APP_Platform AP ON AP.clientId = FW.clientId AND AP.platformType =1)
				BEGIN
					-- For the input client,
					INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams, flags)
					SELECT DISTINCT clientId, 5, '', 16 /*WORK_QUEUE_ITEM_PRE_PROCESSED = 0x10*/
                    FROM #ClientListTable (NOLOCK)
                    WHERE cgId <> 0 AND isExternal=0 AND @clientId=clientId
					INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams, flags)
					SELECT DISTINCT clientId, 5, '', 1
                    FROM #ClientListTable (NOLOCK)
                    WHERE cgId <> 0 AND isExternal=0 AND clientId<>@clientId
					-- This flag will help us exclude CS from processing if present in firewall config file
					SET @ispreProcessingApplicable=1
				END
				ELSE
				BEGIN
					INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
                    SELECT DISTINCT clientId, 5, ''
                    FROM #ClientListTable (NOLOCK)
                    WHERE cgId <> 0 AND isExternal=0
				END
			END
			IF (@ispreProcessingApplicable=0)
			BEGIN
				-- Dont push WQ for clients part of external CG.
				INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT pushFWClientList.clientId, 5, ''
				FROM #ClientListTable pushFWClientList
				INNER JOIN App_client ON pushFWClientList.clientId = App_Client.id
				LEFT JOIN App_clientGroupAssoc ON pushFWClientList.clientId = App_clientGroupAssoc.clientId AND App_clientGroupAssoc.clientGroupId = pushFWClientList.cgId AND pushFWClientList.isExternal=1
				WHERE App_clientGroupAssoc.clientId IS NULL
			END
			ELSE
			BEGIN
				-- Dont push WQ for clients part of external CG and CS (CS will be taken care when we set @ispreProcessingApplicable and flag for one client being WORK_QUEUE_ITEM_PRE_PROCESSED)
				INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
				SELECT pushFWClientList.clientId, 5, ''
				FROM #ClientListTable pushFWClientList
				INNER JOIN App_client ON pushFWClientList.clientId = App_Client.id
				LEFT JOIN App_clientGroupAssoc ON pushFWClientList.clientId = App_clientGroupAssoc.clientId AND App_clientGroupAssoc.clientGroupId = pushFWClientList.cgId AND pushFWClientList.isExternal=1
LEFT JOIN APP_Platform AP ON AP.clientId = pushFWClientList.clientId AND AP.platformType =1
				WHERE App_clientGroupAssoc.clientId IS NULL AND AP.clientId IS NULL
			END
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
			SELECT i.clientId, 26,N'<JobManager_StatelessJobControlWorkTokenParams jobControl="0" sendClientConfigs="1" />'
			FROM #ClientTable i INNER JOIN CommCellCloudLaptopClients cLaptop ON i.clientId = cLaptop.ClientID
		END
		-- Insert workqueue tokens to update DIP entries in archPipelineFirewall table - BEGIN
		IF @insertCount > 0
		BEGIN
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
SELECT 2, 54,
					CAST((
SELECT 4 as '@requestType',
					(
						SELECT
							(SELECT CGAssoc.clientGroupId AS '@clientGroupId' FOR XML PATH('clientGroup'), TYPE),
							(SELECT clientId AS '@clientId'
							FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc1
							WHERE CGAssoc1.clientGroupId = CGAssoc.clientGroupId AND CGAssoc1.opType=2 -- Inserted entries
								AND CGAssoc1.createdTime<=@startTime
							FOR XML PATH('clients'), TYPE)
						FOR XML PATH('clientGroupInfo'),TYPE)
					FOR XML PATH('App_WildcardDIPWorkqueueParams'),TYPE
					) AS NVARCHAR(MAX))
			FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc
			INNER JOIN App_Client (NOLOCK) AC
			ON CGAssoc.clientId=AC.id
			WHERE CGAssoc.opType=2 -- Inserted entries.
				AND CGAssoc.createdTime<=@startTime
			GROUP BY clientGroupId
		END
		IF @deleteCount > 0
		BEGIN
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
SELECT 2, 54,
				   CAST((
SELECT 5 as '@requestType',
					(
						SELECT
							(SELECT CGAssoc.clientGroupId AS '@clientGroupId' FOR XML PATH('clientGroup'), TYPE),
							(SELECT clientId AS '@clientId'
							FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc1
							WHERE CGAssoc1.clientGroupId = CGAssoc.clientGroupId AND CGAssoc1.opType=3 -- Deleted entries
								AND CGAssoc1.createdTime<=@startTime
							FOR XML PATH('clients'), TYPE)
						FOR XML PATH('clientGroupInfo'),TYPE)
					FOR XML PATH('App_WildcardDIPWorkqueueParams'),TYPE
					) AS NVARCHAR(MAX))
			FROM #AppClientCGAssocTrigger (NOLOCK) CGAssoc
			INNER JOIN App_Client (NOLOCK) AC
			ON CGAssoc.clientId=AC.id
			WHERE CGAssoc.opType=3 -- Deleted entries.
				AND CGAssoc.createdTime<=@startTime
			GROUP BY clientGroupId
		END
		-- Insert workqueue tokens to update DIP entries in archPipelineFirewall table - END
		--If Any clients are modified and they have subclients in App_subclientMountPathMapping they need to be notified of the change
		IF @insertCount > 0 OR @deleteCount >0
		BEGIN
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
			SELECT DISTINCT CGAssoc.clientId, 61/*WORK_TOKEN_BACKUP_ACTIVITY_CHANGED*/,''
			 FROM #AppClientCGAssocTrigger CGAssoc
			 INNER JOIN APP_Application APP WITH(NOLOCK) ON APP.clientId = CGAssoc.clientId
			 INNER JOIN APP_SubclientToMountpathMapping SMP WITH(NOLOCK) ON SMP.subclientId = APP.id
			WHERE CGAssoc.opType IN (2, 3) -- Added OR deleted Entries
				AND CGAssoc.createdTime<=@startTime
		END
		-- check if any SQl AlwaysON pseudo clients client is present, if yes submit request to all associated Physicals
		IF EXISTS(SELECT 1 FROM #WorkTokensForWQ C
					  INNER JOIN App_Client APP WITH(NOLOCK) ON C.clientID = APP.id
WHERE APP.status & 0x20000000 <> 0
		)
		BEGIN
			IF object_id('tempdb.dbo.#xmlOption_InfoTable') is not null
				DROP TABLE #xmlOption_InfoTable
			CREATE TABLE #xmlOption_InfoTable(psuedoclientId INT, xmlValue XML)
			CREATE CLUSTERED INDEX xmlOption_InfoTable_psuedoclientId_Index1 ON #xmlOption_InfoTable (psuedoclientId)
			INSERT INTO #xmlOption_InfoTable
			SELECT DISTINCT CL.clientID,IP.attrVal
			FROM #WorkTokensForWQ CL
			JOIN APP_Application APP (NOLOCK) ON APP.clientId = CL.clientID
			JOIN APP_InstanceProp IP (NOLOCK) ON IP.componentNameId = APP.instance
AND APP.appTypeId = 81
			AND IP.attrName = 'Availability Replicas'
			AND IP.modified = 0
			INSERT INTO #WorkTokensForWQ (clientId, workToken, workTokenParams)
			SELECT pc.value('@clientId', 'INT'),workToken, workTokenParams
			FROM #WorkTokensForWQ CL INNER JOIN #xmlOption_InfoTable T ON CL.clientId = T.psuedoclientId
			CROSS APPLY T.xmlValue.nodes('/App_SQLAvailabilityReplicas/SQLAvailabilityReplicasList/replicaClient') P(pc)
			-- Do not push to SQl AlwaysON pseudo clients
			DELETE #WorkTokensForWQ FROM #WorkTokensForWQ
			INNER JOIN #xmlOption_InfoTable ON #WorkTokensForWQ.clientId = #xmlOption_InfoTable.psuedoclientId
			AND workToken!= 36		/*WORK_TOKEN_IDA_PROP_MODIFIED*/
			IF object_id('tempdb.dbo.#xmlOption_InfoTable') is not null
				DROP TABLE #xmlOption_InfoTable
		END
		BEGIN TRANSACTION
		SET @isTranStarted = 1
		INSERT INTO APP_WorkQueueRequest (clientId, workToken, workTokenParams, createTime, lastUpdateTime, retryCount, flag, remoteClient)
		SELECT DISTINCT clientId, workToken, workTokenParams, @currentTime, 0, 0, flags, -1
		FROM #WorkTokensForWQ
		IF @insertCount > 0 OR @deleteCount > 0
		BEGIN
			INSERT INTO APP_ComponentProp (componentType, componentId, propertyTypeId, dataType, longVal, longlongVal, stringVal, created, modified)
			SELECT 1 /* CV_COMPONENT_TYPE_COMMCELL */, 2 /* DEFAULT_COMMCELL_ID */, 2500 /* CV_COMPONENT_CG_SCP_ASSOC_WORK_QUEUE_XML */, 1, 0, 0, CONVERT(NVARCHAR(MAX),@sParam), @currentTime, 0
		END
		DELETE APP_ClientGroupAssocTrigger FROM APP_ClientGroupAssocTrigger
		INNER JOIN #AppClientCGAssocTrigger AppClientCGAssocTrigger ON
		AppClientCGAssocTrigger.ID = APP_ClientGroupAssocTrigger.ID
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
PRINT  'INSIDE CATCH BLOCK WITH FOLLOWING ERROR:
	ERROR CODE: ' + CAST(ERROR_NUMBER() AS VARCHAR) + '
	PROC NAME: ' + ISNULL(ERROR_PROCEDURE(), '???') + '
	ERROR LINE NO: ' + CAST(ERROR_LINE() AS VARCHAR)  + '
	ERROR MESSAGE: ' + ERROR_MESSAGE() + '
	ERROR SEVERITY: ' + CAST(ERROR_SEVERITY() AS VARCHAR) +  '
	ERROR STATE: ' + CAST(ERROR_STATE() AS VARCHAR)
		SET @o_errorCode = 1
		SET @o_errorCodeStr = 'Processing rows from App_clientGroupAssocTrigger failed'
		IF @isTranStarted = 1
		BEGIN
		     ROLLBACK TRANSACTION
		END
	END CATCH
	IF object_id('tempdb.dbo.#ClientTable') IS NOT NULL
	BEGIN
		DROP TABLE #ClientTable
	END
	IF OBJECT_ID('tempdb.dbo.#WorkTokensForWQ') IS NOT NULL
	BEGIN
			DROP TABLE #WorkTokensForWQ
	END
	SELECT @o_errorCode, @o_errorCodeStr
GO

IF EXISTS (select * from GxQscripts where name = 'App_CGAssocTriggerProcessing')
	delete from GxQscripts where name = 'App_CGAssocTriggerProcessing'
GO

IF EXISTS (select * from GXDBVersions where aliasname='App_CGAssocTriggerProcessing')
	delete from GXDBVersions where aliasname = 'App_CGAssocTriggerProcessing'
GO

insert into GXDBVersions values(2, 'App_CGAssocTriggerProcessing',  'v1.1.2.17.8.1', 'App_CGAssocTriggerProcessing', 'v1.1.2.17.8.1')
GO

