问题描述
我一直在解决这个问题,我想防止重复预订的发生.这是我一直在使用的代码:
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;