防止 SQL 中的双重预订 [英] Preventing double bookings in SQL

问题描述

我一直在解决这个问题，我想防止重复预订的发生.这是我一直在使用的代码:

I'm stuck on this problem where I want to prevent double bookings from happening. This is the code I've been using:

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

问题是无论日期是否重叠都会发生错误.我做错了什么?

The problem is that the error happens whether or not the dates are overlapping. What am I doing wrong?

推荐答案

本声明:

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 是否有问题?

Is highly suspect. You are assigning variables in the select and using the same variables in the where. Is there an issue with expressing this as a normal 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 甚至没有使用它.我认为以下是您想要的触发器主体:

This still doesn't do what you want for two reasons. First, this logic is incorrect. And two, the if isn't even using it. I think the following is what you want for the body of the trigger:

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;

这篇关于防止 SQL 中的双重预订的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！