USE [Yolo880_Hotel]
GO
/****** Object: Trigger [dbo].[update_F_Status] Script Date: 2023-06-18 22:31:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[update_book_time]
on [dbo].[T_RoomOrder]
for update
AS
BEGIN
Declare @dtt varchar(20);
Declare @keyid varchar(20);
Declare @fid varchar(100);
Declare @edt varchar(20);
Declare @Status varchar(10);
Declare @i int;
if (UPDATE(F_BeginDateTime) or UPDATE(F_EndDateTime) )
begin
DECLARE tmpOrder CURSOR for select F_ID,F_KeyId,F_Status,CONVERT(varchar(19), F_BeginDateTime, 120),CONVERT(varchar(19), F_EndDateTime, 120) from inserted
OPEN tmpOrder
FETCH NEXT FROM tmpOrder into @fid,@keyid,@Status,@dtt,@edt
WHILE (@@FETCH_STATUS=0)
BEGIN
if ( @Status='预订')
begin
SELECT @i = count(*) FROM T_RoomOrder
WHERE
(
(F_BeginDateTime <= @dtt and @dtt <= F_EndDateTime and @edt >= F_EndDateTime)OR
(F_BeginDateTime <= @dtt and @dtt <= F_EndDateTime and F_EndDateTime >= @edt)OR
(F_BeginDateTime >= @dtt and @edt >= F_EndDateTime)OR
(F_BeginDateTime >= @dtt and F_BeginDateTime <= @edt AND @edt <= F_EndDateTime)
) AND (F_Status = '入住' OR F_Status = '预订') and F_ID <> @fid and F_KeyId =@keyid
if (@i>0)
begin
RAISERROR ('
预订时间冲突错误提示:
修改的时间有和其他房间冲房的风险，请修改为其他时间后重试。
如有疑问,请联系售后电话:0371-55981330、0371-53311391', 16, 1)
ROLLBACK;
INSERT INTO dbo.T_Log_RoomSvrc ( F_EventType ,F_Content, F_TableName,F_table_FID )
values( '修改订单时间失败','房号:'+@keyid + ' ,到店时间:' + @dtt + ' ,离店时间:' + @edt ,'T_RoomOrder',@fid )
CLOSE tmpOrder
DEALLOCATE tmpOrder
return
end
else
begin
CLOSE tmpOrder
DEALLOCATE tmpOrder
return
end
end
FETCH NEXT FROM tmpOrder into @fid,@keyid,@Status,@dtt,@edt
END
CLOSE tmpOrder
DEALLOCATE tmpOrder
end
END
