防止SQL中的双重预订

一尘不染

防止SQL中的双重预订

sql

我被困在这个问题上，以防止发生重复预订的情况。这是我一直在使用的代码：

USE INL5
GO

SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO

ALTER TRIGGER [dbo].[trg_bookinginfo_doublebooking] ON [dbo].[bookinginfo] 
FOR INSERT AS 
DECLARE @startdate AS DATE 
DECLARE @enddate AS DATE 
DECLARE @roomnumber AS CHAR(3)

SELECT @startdate = inserted.startdate, @enddate = inserted.enddate, @roomnumber = inserted.roomnumber 
FROM inserted, bookinginfo 
WHERE @roomnumber = bookinginfo.roomnumber AND (@startdate BETWEEN bookinginfo.startdate AND bookinginfo.enddate) AND (@enddate BETWEEN bookinginfo.startdate AND bookinginfo.enddate) 
IF EXISTS(SELECT * FROM inserted)

BEGIN RAISERROR ('Double bookings are not allowed',16,1) 
ROLLBACK TRANSACTION 
END

问题在于，无论日期是否重叠，都会发生错误。我究竟做错了什么？

阅读 166

2021-05-16

共1个答案

一尘不染

这个说法：

SELECT @startdate = inserted.startdate, @enddate = inserted.enddate, 
       @roomnumber = inserted.roomnumber 
FROM inserted, bookinginfo 
WHERE @roomnumber = bookinginfo.roomnumber AND
     (@startdate BETWEEN bookinginfo.startdate AND bookinginfo.enddate) AND 
     (@enddate BETWEEN bookinginfo.startdate AND bookinginfo.enddate)

是非常可疑的。您要在中分配变量，select并在中使用相同的变量where。将其表示为正常内容是否有问题join？

SELECT @startdate = i.startdate, @enddate = i.enddate, @roomnumber = i.roomnumber 
FROM inserted i JOIN
     bookinginfo bi
     ON i.roomnumber = bi.roomnumber AND
        (i.startdate BETWEEN bi.startdate AND bi.enddate) AND 
        (i.enddate BETWEEN bi.startdate AND bi.enddate) AND
        i.BookinginfoID <> bi.BookinginfoID;

这仍然不能满足您的需求，原因有两个。首先，这种逻辑是不正确的。第二，if甚至没有使用它。我认为以下是触发器主体的要求：

IF (EXISTS (SELECT 1
            FROM inserted i JOIN
                 bookinginfo bi
                 ON i.roomnumber = bi.roomnumber AND
                    i.startdate <= bi.enddate AND 
                    i.enddate >= bi.startdate AND
                    i.BookinginfoID <> bi.BookinginfoID;
           )
BEGIN
     RAISERROR ('Double bookings are not allowed',16,1)
     . . . 
END;

2021-05-16