
use control

/*
 * Trigger used to release tieline reservations for other tielines in a tieline grouping.
 * 
 * Note this is called one with a result set of deleted rows passed in. We need to use a cursor to
 * iterate through all the deleted row. Surprise!
 */

IF EXISTS (SELECT name FROM sysobjects WHERE NAME='NV_ReleaseTieline2GroupTrigger')
    DROP TRIGGER NV_ReleaseTieline2GroupTrigger
go

CREATE TRIGGER NV_ReleaseTieline2GroupTrigger
ON Tieline2Reservation

FOR DELETE
AS

SET NOCOUNT ON;

DECLARE @vcharDebugStmt varchar(200)
SET @vcharDebugStmt = 'echo Entering NV_ReleaseTieline2GroupTrigger  >> c:\SQLDebugFile'
EXEC master..xp_cmdshell @vcharDebugStmt

DECLARE @intTieline2Reservation_Tieline2 integer
DECLARE @vcharTieline2Reservation_ControlSystem_Name varchar(24)
DECLARE @intTieline2Reservation_User integer
DECLARE @vchTieline2Reservation_LTC varchar(20)

-- Iterate through all the deleted rows
DECLARE cursorDeletedTielines CURSOR  LOCAL FOR 
	SELECT Tieline2Reservation_Tieline2, Tieline2Reservation_ControlSystem_Name, Tieline2Reservation_User,
		Tieline2Reservation_LTC
	FROM Deleted

OPEN cursorDeletedTielines
	
FETCH NEXT FROM cursorDeletedTielines INTO @intTieline2Reservation_Tieline2, 
	@vcharTieline2Reservation_ControlSystem_Name, @intTieline2Reservation_User, @vchTieline2Reservation_LTC
	
WHILE (  @@FETCH_STATUS = 0 )
BEGIN

	SET @vcharDebugStmt = 'echo In NV_ReleaseTieline2GroupTrigger processing deleted.Tieline2Reservation_Tieline2: ' + CAST( @intTieline2Reservation_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
	EXEC master..xp_cmdshell @vcharDebugStmt 

	/* Is this Tieline in a Group? Assuption: A Tieline can only be in one group */
	DECLARE @intCount integer
	SELECT @intCount = COUNT(*) FROM Tieline2GroupBinding WHERE Tieline2GroupBinding_Tieline2 = @intTieline2Reservation_Tieline2
	IF ( @intCount = 1 )
	BEGIN
	 

		DECLARE @intTieline2GroupBinding_Tieline2Group int
		DECLARE @intTieline2GroupBinding_Tieline2		 int
  
		SELECT @intTieline2GroupBinding_Tieline2Group = Tieline2GroupBinding_Tieline2Group FROM Tieline2GroupBinding 
			WHERE Tieline2GroupBinding_Tieline2 = @intTieline2Reservation_Tieline2
  
		SET @vcharDebugStmt = 'echo In NV_ReleaseTieline2GroupTrigger processing TielineGroup: ' + CAST( @intTieline2GroupBinding_Tieline2Group AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
		EXEC master..xp_cmdshell @vcharDebugStmt 
  
		DECLARE @intDestPhysLevel int
		DECLARE @intDestPort 		int
  

		-- Iterate through all the tielines in the grouping
		DECLARE cursorTielineGroup  CURSOR  LOCAL FOR 
			SELECT Tieline2GroupBinding_Tieline2
			FROM Tieline2GroupBinding
			WHERE
				Tieline2GroupBinding_Tieline2Group = @intTieline2GroupBinding_Tieline2Group AND
				Tieline2GroupBinding_Tieline2 <> @intTieline2Reservation_Tieline2
				order by Tieline2GroupBinding_Tieline2
	
		OPEN cursorTielineGroup
	
		FETCH NEXT FROM cursorTielineGroup INTO @intTieline2GroupBinding_Tieline2
	
		WHILE (  @@FETCH_STATUS = 0 )
		BEGIN
				SET @vcharDebugStmt = 'echo In NV_ReleaseTieline2GroupTrigger processing Grouped Tieline: ' + CAST( @intTieline2GroupBinding_Tieline2 AS VARCHAR(6) ) + ' >> c:\SQLDebugFile'
				EXEC master..xp_cmdshell @vcharDebugStmt
			
				delete from Tieline2Reservation where 
					Tieline2Reservation_Tieline2 = @intTieline2GroupBinding_Tieline2 AND 
					Tieline2Reservation_LTC LIKE @vchTieline2Reservation_LTC;
					
				FETCH NEXT FROM cursorTielineGroup INTO @intTieline2GroupBinding_Tieline2
		END
	
		CLOSE cursorTielineGroup
		DEALLOCATE cursorTielineGroup

	END
	
	-- Fetch next deletd row
	FETCH NEXT FROM cursorDeletedTielines INTO @intTieline2Reservation_Tieline2, 
		@vcharTieline2Reservation_ControlSystem_Name, @intTieline2Reservation_User, @vchTieline2Reservation_LTC
END
	
CLOSE cursorDeletedTielines
DEALLOCATE cursorDeletedTielines

SET @vcharDebugStmt = 'echo Exiting NV_ReleaseTieline2GroupTrigger >> c:\SQLDebugFile'
EXEC master..xp_cmdshell @vcharDebugStmt

GO