Patel Akash

Patel Akash

  • 1.6k
  • 222
  • 1.5k

Update Statement Not Working inside while loop

Sep 15 2019 11:38 AM
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

Answers (1)