Update Statement Not Working inside while loop

Question

CREATE trigger [dbo].[tr_tbl_ConferenceBookingMaster_ForUpdate]

on [dbo].[tbl_ConferenceBookingMaster]

AFTER update

as

Begin

Declare @ID int

Declare @JudgeId int

Declare @Start_Date datetime

Declare @End_Date datetime

Declare @Status nvarchar(50)

 

Select @ID=ConferenceRoom,@JudgeId=JudgeId,@Start_Date=[Start_Date],@End_Date= End_Date,

@Status=[Status] from deleted

 

if(@Status = 'Confirm')

BEGIN

Declare @Start int

Declare @End int

Declare @MyStartdate datetime

Declare @MyEnddate datetime

Declare @Conference int

Declare @MyId int

SET @Start=1

 

Select @End=COUNT(ID) from tbl_ConferenceBookingMaster

Where ([Status]='Waiting')

and ConferenceRoom=@ID

and ( [Start_Date]>@Start_Date and [Start_Date] < @End_Date

OR End_Date >@Start_Date and [Start_Date] < @End_Date)

 

WHILE (@Start <= @End)

BEGIN

 

Select @MyId = ID,@MyStartdate=[Start_Date],@MyEnddate=End_Date,@Conference=ConferenceRoom FROM (

SELECT ROW_NUMBER() OVER (ORDER by CreatedDate) As RowCol,*

from

tbl_ConferenceBookingMaster

Where ([Status]='Waiting')

and ConferenceRoom=@ID

and ( [Start_Date]>@Start_Date and [Start_Date] < @End_Date

OR End_Date >@Start_Date and [Start_Date] < @End_Date)) t2 

Where t2.RowCol=@Start

 

IF(EXISTS (Select 1 from tbl_ConferenceBookingMaster

Where ([Status]='Waiting')

and ConferenceRoom=@Conference

and ( [Start_Date]>@MyStartdate and [Start_Date] < @MyEnddate

OR End_Date >@MyStartdate and [Start_Date] < @MyEnddate)))

BEGIN

UPDATE tbl_ConferenceBookingMaster SET Status='Confirm'

WHERE ID=8

END

ELSE

BEGIN

UPDATE tbl_ConferenceBookingMaster SET Status='Waiting'

WHERE ID=8

END

SET @Start=@Start+1;

 

END

END

End