USE ManageSoft
GO

ALTER  PROCEDURE ThreatToCategoryPutBatchByThreatIDThreatCategoryID
AS
	DECLARE @count AS int
	SET @count = 0

	CREATE INDEX [TempThreatToCategoryThreatIDThreatCategoryID]
		ON #ThreatToCategory (ThreatID, ThreatCategoryID)

	BEGIN TRANSACTION

	INSERT ThreatToCategory (
		ThreatID,
		ThreatCategoryID
	)
		SELECT	ThreatID,
			ThreatCategoryID
		FROM	#ThreatToCategory AS t
		WHERE NOT EXISTS(	-- Current record doesn't exist
			SELECT * FROM ThreatToCategory
			WHERE	ThreatToCategory.ThreatID = t.ThreatID
			  AND	ThreatToCategory.ThreatCategoryID = t.ThreatCategoryID
		)
		AND EXISTS(
			SELECT * FROM Threat
			WHERE	Threat.ThreatID = t.ThreatID
		)
		AND EXISTS(
			SELECT * FROM ThreatCategory
			WHERE ThreatCategory.ThreatCategoryID = t.ThreatCategoryID
		)
	SET @count = @count + @@ROWCOUNT
	DROP TABLE #ThreatToCategory	-- Drop the temporary table
	COMMIT WORK
	RETURN @count

GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'ThreatToCategoryRemoveByThreatIDThreatCategoryID')
	DROP PROCEDURE ThreatToCategoryRemoveByThreatIDThreatCategoryID
GO
CREATE PROCEDURE ThreatToCategoryRemoveByThreatIDThreatCategoryID
	@ThreatID		int,
	@ThreatCategoryID	int
AS
	DELETE	ThreatToCategory
	WHERE	ThreatID = @ThreatID
	  AND	ThreatCategoryID = @ThreatCategoryID
GO

ALTER  PROCEDURE ThreatToScannerPutBatchByThreatIDThreatScannerID
AS
	DECLARE @count AS int
	SET @count = 0

	CREATE INDEX [TempThreatToScannerThreatIDThreatScannerID]
		ON #ThreatToScanner (ThreatID, ThreatScannerID)

	BEGIN TRANSACTION

	INSERT ThreatToScanner (
		ThreatID,
		ThreatScannerID
	)
		SELECT	ThreatID,
			ThreatScannerID
		FROM	#ThreatToScanner AS t
		WHERE NOT EXISTS(	-- Current record doesn't exist
			SELECT * FROM ThreatToScanner
			WHERE	ThreatToScanner.ThreatID = t.ThreatID
			  AND	ThreatToScanner.ThreatScannerID = t.ThreatScannerID
		)
		AND EXISTS(
			SELECT * FROM Threat
			WHERE	Threat.ThreatID = t.ThreatID
		)
		AND EXISTS(
			SELECT * FROM ThreatScanner
			WHERE ThreatScanner.ThreatScannerID = t.ThreatScannerID
		)
	SET @count = @count + @@ROWCOUNT
	DROP TABLE #ThreatToScanner	-- Drop the temporary table
	COMMIT WORK
	RETURN @count

GO

IF EXISTS(SELECT * FROM sysobjects WHERE xtype = 'P'
		AND name = 'ThreatToScannerRemoveByThreatIDThreatScannerID')
	DROP PROCEDURE ThreatToScannerRemoveByThreatIDThreatScannerID
GO
CREATE PROCEDURE ThreatToScannerRemoveByThreatIDThreatScannerID
	@ThreatID		int,
	@ThreatScannerID	int
AS
	DELETE	ThreatToScanner
	WHERE	ThreatID = @ThreatID
	  AND	ThreatScannerID = @ThreatScannerID
GO