trg_IncreasePriority.sql

CREATE TRIGGER trg_IncreasePriority
ON Requisitions
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Email VARCHAR(128), @Reserv_ID INT, @ReturnedAt DATETIME, @Started_At DATETIME, @ReservPeriod INT;
    DECLARE @CurrentPriority INT, @BasePriority INT, @NewPriority INT;

    -- Getting data from the updated record
    SELECT 
        @Reserv_ID = ins.Reserv_ID,
        @ReturnedAt = GETDATE()
    FROM Inserted ins
    JOIN States s ON ins.Status = s.State_ID
    WHERE s.Name = 'Closed';

    -- Receive reservation data
    SELECT 
        @Email = r.Email,
        @Started_At = r.Started_At,
        @ReservPeriod = r.ReservPeriod
    FROM Reservations r
    WHERE r.Reserv_ID = @Reserv_ID;

    -- Checking whether the resource has been delivered on time
    IF @ReturnedAt <= DATEADD(HOUR, @ReservPeriod, @Started_At) -- Delivered on time
    BEGIN
        -- Get the current and base priority of the user
        SELECT 
            @CurrentPriority = u.CurrentPriority,
            @BasePriority = u.BasePriority
        FROM Users u
        WHERE u.Email = @Email;

        -- Increase the priority by 1 if it is less than the base priority
        IF @CurrentPriority < @BasePriority
        BEGIN
            SET @NewPriority = @CurrentPriority + 1;

            UPDATE Users
            SET CurrentPriority = @NewPriority
            WHERE Email = @Email;
        END;
    END;
END;