問題描述

我一直在解決這個問題，我想防止重復預訂的發生.這是我一直在使用的代碼:

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 中的雙重預訂的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！