Trigger is not working properly in SQL Server-CodePudding

I understand that perhaps the problem is that I use a select on the same table that I update or insert a record, but this trigger throws an exception in most cases. Then what should I rewrite?

The purpose of the trigger is to block inserting or updating entries if the room is already occupied on a certain date, i.e. the dates overlap

CREATE TABLE [dbo].[settlements]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [client_id] [int] NOT NULL,
    [checkin_date] [date] NOT NULL,
    [checkout_date] [date] NOT NULL,
    [room_id] [int] NOT NULL,
    [employee_id] [int] NULL
);

ALTER TRIGGER [dbo].[On_Hotel_Settlement]
ON [dbo].[settlements]
AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @room_id int
    DECLARE @checkin_date Date, @checkout_date Date

    DECLARE cursor_settlement CURSOR FOR 
        SELECT room_id, checkin_date, checkout_date
        FROM inserted;

    OPEN cursor_settlement;

    FETCH NEXT FROM cursor_settlement INTO @room_id, @checkin_date, @checkout_date;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF EXISTS (SELECT 1  
                   FROM settlements AS s    
                   WHERE s.room_id = @room_id 
                     AND ((s.checkin_date >= @checkin_date AND  s.checkin_date <= @checkout_date)
                          OR (s.checkout_date >= @checkin_date AND s.checkout_date <= @checkout_date)))  
        BEGIN  
            RAISERROR ('Room is not free', 16, 1);  
            ROLLBACK;
        END;

        FETCH NEXT FROM cursor_settlement INTO @room_id, @checkin_date, @checkout_date;
    END;

    CLOSE cursor_settlement;
    DEALLOCATE cursor_settlement;
    RETURN

I tried to test the code by removing the condition with dates and leaving only the room _id, but the trigger does not work correctly in this case either.

I tried query like

IF EXISTS (SELECT 1  
           FROM settlements AS s    
           WHERE s.room_id = 9
             AND ((s.checkin_date >= '2022-12-10' AND  s.checkin_date <= '2022-12-30')
                  OR (s.checkout_date >= '2022-12-10' AND s.checkout_date <= '2022-12-30')))  
BEGIN  
    RAISERROR ('Room is not free', 16, 1);  
END;

and it worked correctly. Problem is that is not working in my trigger

CodePudding user response：

As noted by comments on the original post above, the cursor loop is not needed and would best be eliminated to improve efficiency.

As for the date logic, consider a new record with a check-in date that is the same as the checkout date from a prior record. I believe that your logic will consider this an overlap and throw an error.

My suggestion is that you treat the check-in date as inclusive (that night is in use) and the checkout date as exclusive (that night is not in use).

A standard test for overlapping dates would then be Checkin1 < Checkout2 AND Checkin2 < Checkout1. (Note use of inequality.) It may not be obvious, but this test covers all overlapping date cases. (It might be more obvious if this condition is inverted and rewritten as NOT (Checkin1 >= Checkout2 OR Checkin2 >= Checkout1).)

~~Also, if you are inserting multiple records at once, I would suggest that you also check the inserted records for mutual conflicts.~~

Suggest something like:

ALTER TRIGGER [dbo].[On_Hotel_Settlement]
   ON [dbo].[settlements]
   AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF EXISTS(
        SELECT *
        FROM inserted i
        JOIN settlements s
            ON s.room_id = i.room_id
            AND s.checkin_date < i.checkout_date
            AND i.checkin_date < s.checkout_date
            AND s.id <> i.id
    )
    BEGIN  
        RAISERROR ('Room is not free', 16, 1);  
        ROLLBACK;
    END;

    RETURN;
END

One more note: Be careful with an early rollback of a transaction. If your overall logic could potentially execute additional DML after the error is thrown, that would now execute outside the transaction and there would be no remaining transaction to roll back.