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
Priyanka JainPosted Sep 15, 2019, 1:18 PM