

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Trigger/APP_CCSComponentPropTableChange.trigger] ---------- 


SET NOCOUNT ON


print '... Creating Trigger: APP_CCSComponentPropTableChange'
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[APP_CCSComponentPropTableChange]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
BEGIN
  drop trigger [dbo].[APP_CCSComponentPropTableChange]
  delete from GXDBVersions where name = 'APP_CCSComponentPropTableChange' and type = 6
END
GO

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER APP_CCSComponentPropTableChange
ON APP_ComponentProp
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
	SET NOCOUNT ON
	-- IF Temp Table exist then it is a CCM Operation and CCS should do nothing
	-- with the CCM Data being inserted into the Commserv Database
	IF OBJECT_ID('tempdb..#CCM_IMPORT_IS_RUNNING') IS NOT NULL
	BEGIN
		RETURN;
	END
	DECLARE @Filters TABLE  (
		propType        INT
	)
	INSERT INTO @Filters (propType)
		SELECT
			f.value('@value', 'INT') filter
		FROM APP_CCSXMLMapping WITH(NOLOCK)
			CROSS APPLY filters.nodes('/Filters/Column/Filter') n(f)
		WHERE
			id = 20
			AND f.value('../@name', 'NVARCHAR(128)') = 'propertyTypeId'
			AND f.value('../@opType', 'NVARCHAR(128)') = 'EXCLUDE'
	DECLARE	@currentTime INT = dbo.GetUnixTime(GetUTCDate())
	DECLARE @TableType INT = 20		-- APP_ComponentProp Table Type
	DECLARE @AllClients INT = -1
	INSERT INTO APP_CCSTriggerRows (clientId, tableType, opCode, modified, rowId1)
		SELECT
			clientId,
			tableType,
			opCode,
			modified,
			rowId1
		FROM (
				-- CommCell Type
				SELECT
						@AllClients clientId, @TableType tableType, 0 opCode, @currentTime modified, i.id rowId1, i.propertyTypeId
					FROM
						INSERTED i
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 1	--CommCell
						AND i.propertyTypeId IN (1, 10, 11, 12, 13)	-- CV_COMPONENT_COMMCELL_CI_ENGINE_NAME_PROP,CV_COMPONENT_COMMCELL_CA_CERTIFICATE,CV_COMPONENT_COMMCELL_CA_RSA_PRIVATE,CV_COMPONENT_COMMCELL_COMMCELL_CERTIFICATE,CV_COMPONENT_COMMCELL_COMMCELL_RSA_PRIVATE
				UNION
				SELECT
						@AllClients, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 1	--CommCell
						AND i.propertyTypeId IN (1, 10, 11, 12, 13)	-- CV_COMPONENT_COMMCELL_CI_ENGINE_NAME_PROP,CV_COMPONENT_COMMCELL_CA_CERTIFICATE,CV_COMPONENT_COMMCELL_CA_RSA_PRIVATE,CV_COMPONENT_COMMCELL_COMMCELL_CERTIFICATE,CV_COMPONENT_COMMCELL_COMMCELL_RSA_PRIVATE
				UNION
				SELECT
						@AllClients, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 1	--CommCell
						AND d.propertyTypeId IN (1, 10, 11, 12, 13)	-- CV_COMPONENT_COMMCELL_CI_ENGINE_NAME_PROP,CV_COMPONENT_COMMCELL_CA_CERTIFICATE,CV_COMPONENT_COMMCELL_CA_RSA_PRIVATE,CV_COMPONENT_COMMCELL_COMMCELL_CERTIFICATE,CV_COMPONENT_COMMCELL_COMMCELL_RSA_PRIVATE
				-- Client Type
				UNION
				SELECT
						i.componentId, @TableType, 0, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 3	--Client
				UNION
				SELECT
						i.componentId, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 3	--Client
				UNION
				SELECT
						d.componentId, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 3	--Client
				-- IDA Type
				UNION
				SELECT
						n.clientId, @TableType, 0, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_IDAName n WITH(NOLOCK)
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 4	--IDA
						AND n.id = i.componentId
				UNION
				SELECT
						n.clientId, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_IDAName n WITH(NOLOCK)
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 4	--IDA
						AND n.id = i.componentId
				UNION
				SELECT
						n.clientId, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d,
						APP_IDAName n WITH(NOLOCK)
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 4	--IDA
						AND n.id = d.componentId
				-- BackupSet Type
				UNION
				SELECT
						a.clientId, @TableType, 0, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_Application a WITH(NOLOCK)
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 5	--BackupSet
						AND a.backupSet = i.componentId
				UNION
				SELECT
						a.clientId, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_Application a WITH(NOLOCK)
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 5	--BackupSet
						AND a.backupSet = i.componentId
				UNION
				SELECT
						a.clientId, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d,
						APP_Application a WITH(NOLOCK)
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 5	--BackupSet
						AND a.backupSet = d.componentId
				-- SubClient Type
				UNION
				SELECT
						a.clientId, @TableType, 0, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_Application a WITH (NOLOCK)
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 6	--SubClient
						AND a.id = i.componentId
				UNION
				SELECT
						a.clientId, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i,
						APP_Application a WITH (NOLOCK)
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 6	--SubClient
						AND a.id = i.componentId
				UNION
				SELECT
						a.clientId, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d,
						APP_Application a WITH (NOLOCK)
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 6	--SubClient
						AND a.id = d.componentId
				-- Client Group Type
				UNION
				SELECT
						cga.clientId, @TableType, 0, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i
						INNER JOIN APP_ClientGroupAssoc cga WITH(NOLOCK) ON
							cga.clientGroupId = i.componentId
					WHERE
						i.id NOT IN (SELECT d.id FROM DELETED d)	-- new rows inserted
						AND i.componentType = 8	--ClientGroup
						AND i.propertyTypeId IN (3620, 3621, 3622)	-- CV_COMPONENT_PROP_WIN_FILTERS, CV_COMPONENT_PROP_UNIX_FILTERS, CV_COMPONENT_PROP_USE_CLIENT_GROUP_FILTER
				UNION
				SELECT
						cga.clientId, @TableType, 1, @currentTime, i.id, i.propertyTypeId
					FROM
						INSERTED i
						INNER JOIN APP_ClientGroupAssoc cga WITH(NOLOCK) ON
							cga.clientGroupId = i.componentId
					WHERE
						i.id IN (SELECT d.id FROM DELETED d)		-- updated rows
						AND i.componentType = 8	--ClientGroup
						AND i.propertyTypeId IN (3620, 3621, 3622)	-- CV_COMPONENT_PROP_WIN_FILTERS, CV_COMPONENT_PROP_UNIX_FILTERS, CV_COMPONENT_PROP_USE_CLIENT_GROUP_FILTER
				UNION
				SELECT
						cga.clientId, @TableType, 2, @currentTime, d.id, d.propertyTypeId
					FROM
						DELETED d
						INNER JOIN APP_ClientGroupAssoc cga WITH(NOLOCK) ON
							cga.clientGroupId = d.componentId
					WHERE
						d.id NOT IN (SELECT i.id FROM INSERTED i)	-- existing rows deleted
						AND d.componentType = 8	--ClientGroup
						AND d.propertyTypeId IN (3620, 3621, 3622)	-- CV_COMPONENT_PROP_WIN_FILTERS, CV_COMPONENT_PROP_UNIX_FILTERS, CV_COMPONENT_PROP_USE_CLIENT_GROUP_FILTER
			) q
		WHERE
			q.propertyTypeId NOT IN (
				SELECT
					propType
				FROM @Filters
			)
	-- ComponentTypes Not processed ROOT(0), FASTINDEX_CLOUD(2), EXMB(7), LN_DOC(9)
END


GO
declare @retCode integer		-- Now do Error Check
set @retCode = @@error
if (@retCode <> 0)
BEGIN
	set @retCode = @@error;
	print 'RETVAL: 04 Trigger [APP_CCSComponentPropTableChange]'
END
GO


if NOT EXISTS (SELECT * FROM GXDBVersions WHERE type = 6 AND name = 'APP_CCSComponentPropTableChange' AND revision = '1.1.52.5.8.1')

  insert into GXDBVersions values(6, 'APP_CCSComponentPropTableChange',  '1.1.52.5.8.1', 'APP_CCSComponentPropTableChange', '')
GO

declare @retCode integer		-- Now do Error Check
set @retCode = @@error
if (@retCode <> 0)
BEGIN
	set @retCode = @@error;
	print 'RETVAL: 10 Table [GXDBVersions]'
END
GO


SET NOCOUNT OFF

