Триггер, предотвращающий обновление столбца в зависимости от результата пользовательской функции.

У нас есть компания по прокату DVD. В этом конкретном сценарии мы рассматриваем только таблицы «Член», «Аренда» и «Членство».

Задача состоит в том, чтобы написать триггер, который предотвращает отправку DVD клиенту, если он достиг своего ежемесячного лимита на аренду DVD в соответствии с его членским контрактом с использованием этой функции.

Мой триггер приводит к бесконечному циклу. Он работает без цикла While, но тогда он не работает должным образом, если я рассматриваю несколько обновлений в таблице аренды. Где я не прав?

-- do not run, infinite loop
CREATE OR ALTER TRIGGER trg_Rental_StopDvdShip
ON RENTAL
FOR UPDATE
AS
BEGIN
    DECLARE @MemberId INT
    DECLARE @RentalId INT
    SELECT * INTO #TempTable FROM inserted
    WHILE (EXISTS (SELECT RentalId FROM #TempTable))
    BEGIN
        IF UPDATE(RentalShippedDate)
        BEGIN
            IF (SELECT TotalDvdLeft FROM dvd_numb_left(@MemberId)) <= 0
            BEGIN
                ROLLBACK
                RAISERROR ('YOU HAVE REACHED MONTHLY LIMIT FOR DVD RENTALS', 16, 1)
            END;
        END;
        DELETE FROM #TempTable WHERE RentalID = @RentalId
    END;
END;

Моя функция выглядит следующим образом:


CREATE OR ALTER FUNCTION dvd_numb_left(@member_id INT)
RETURNS @tab_dvd_numb_left TABLE(MemberId INT, Name VARCHAR(50), TotalDvdLeft INT, AtTimeDvdLeft INT)
AS
BEGIN
    DECLARE @name VARCHAR(50)
    DECLARE @dvd_total_left INT
    DECLARE @dvd_at_time_left INT
    DECLARE @dvd_limit INT
    DECLARE @dvd_rented INT
    DECLARE @dvd_at_time INT
    DECLARE @dvd_on_rent INT
    SET @dvd_limit = (SELECT Membership.MembershipLimitPerMonth FROM Membership
        WHERE Membership.MembershipId = (SELECT Member.MembershipId FROM Member WHERE Member.MemberId = @member_id))
    SET @dvd_rented = (SELECT COUNT(Rental.MemberId) FROM Rental
        WHERE CONCAT(month(Rental.RentalShippedDate), '.', year(Rental.RentalShippedDate)) = CONCAT(month(GETDATE()), '.', year(GETDATE())) AND Rental.MemberId = @member_id)
    SET @dvd_at_time = (SELECT Membership.DVDAtTime FROM Membership
        WHERE Membership.MembershipId = (SELECT Member.MembershipId FROM Member WHERE Member.MemberId = @member_id))
    SET @dvd_on_rent = (SELECT COUNT(Rental.MemberId) FROM Rental
        WHERE Rental.MemberId = @member_id AND Rental.RentalReturnedDate IS NULL)
    SET @name = (SELECT CONCAT(Member.MemberFirstName, ' ', Member.MemberLastName) FROM Member WHERE Member.MemberId = @member_id)
    SET @dvd_total_left = @dvd_limit - @dvd_rented
    SET @dvd_at_time_left = @dvd_at_time - @dvd_on_rent
    IF @dvd_total_left < 0
    BEGIN
        SET @dvd_total_left = 0
        SET @dvd_at_time_left = 0
        INSERT INTO @tab_dvd_numb_left(MemberId, Name, TotalDvdLeft, AtTimeDvdLeft)
        VALUES(@member_id, @name, @dvd_total_left, @dvd_at_time_left)
        RETURN;
    END
    INSERT INTO @tab_dvd_numb_left(MemberId, Name, TotalDvdLeft, AtTimeDvdLeft)
    VALUES(@member_id, @name, @dvd_total_left, @dvd_at_time_left)
    RETURN;
END;

Буду рад любому совету.


sql sql-server database-trigger
person Koke Abeke    schedule 13.04.2020    source источник
comment
И в вашем коде есть предположение, что член имеет единственное членство. IME очень часто членство истекает, обновляется, удаляется, а затем снова принимается. Другими словами, один член может иметь несколько членств. Возможно, в любой момент времени активно только одно членство, но вы, кажется, приравниваете два.   -  person SMor    schedule 13.04.2020
comment
О, я вижу, готово, я не знал, спасибо   -  person Koke Abeke    schedule 14.04.2020

Ответы (2)


arrow_upward
2
arrow_downward

Основная проблема заключается в том, что, даже если вы заполняете #TempTable, вы никогда не получаете из него никаких значений.

CREATE OR ALTER TRIGGER trg_Rental_StopDvdShip
ON RENTAL
FOR UPDATE
AS
BEGIN
    DECLARE @MemberId INT, @RentalId INT;

    -- Move test for column update to the first test as it applies to the entire update, not per row.
    IF UPDATE(RentalShippedDate)
    BEGIN
        SELECT * INTO #TempTable FROM inserted;
        WHILE (EXISTS (SELECT RentalId FROM #TempTable))
        BEGIN
            -- Actually pull some information from #TempTable - this wasn't happening before
            SELECT TOP 1 @RentalID = RentalId, @MemberId = MemberId FROM  #TempTable;

            -- Select our values to its working
            -- SELECT @RentalID, @MemberId;

            IF (SELECT TotalDvdLeft FROM dvd_numb_left(@MemberId)) <= 0
            BEGIN
                ROLLBACK
                RAISERROR ('YOU HAVE REACHED MONTHLY LIMIT FOR DVD RENTALS', 16, 1)
            END;

            -- Delete the current handled row
            DELETE FROM #TempTable WHERE RentalID = @RentalId
        END;

        -- For neatness I always drop temp tables, makes testing easier also
        DROP TABLE #TempTable;
    END;
END;

Простой способ отладки таких триггеров - скопировать T-SQL и затем создать переменную таблицы @Inserted, например.

DECLARE @Inserted table (RentalId INT, MemberId INT);

INSERT INTO @Inserted (RentalId, MemberId)
VALUES (1, 1),  (2, 2);

    DECLARE @MemberId INT, @RentalId INT;

    -- Move test for column update to the first test as it applies to the entire update, not per row.
    -- IF UPDATE(RentalShippedDate)
    BEGIN
        SELECT * INTO #TempTable FROM @inserted;
        WHILE (EXISTS (SELECT RentalId FROM #TempTable))
        BEGIN
            -- Actually pull some information from #TempTable - this wasn't happening before
            SELECT TOP 1 @RentalID = RentalId, @MemberId = MemberId FROM  #TempTable;

            -- Select our values to its working
            SELECT @RentalID, @MemberId;

            -- IF (SELECT TotalDvdLeft FROM dvd_numb_left(@MemberId)) <= 0
            -- BEGIN
            --     ROLLBACK
            --     RAISERROR ('YOU HAVE REACHED MONTHLY LIMIT FOR DVD RENTALS', 16, 1)
            -- END;

            -- Delete the current handled row
            DELETE FROM #TempTable WHERE RentalID = @RentalId
        END;

        -- For neatness I always drop temp tables, makes testing easier also
        DROP TABLE #TempTable;
    END;

Примечание. throw - это рекомендуемый способ выдать ошибку вместо raiserror.

person Dale K    schedule 13.04.2020

arrow_upward
1
arrow_downward

Еще одна вещь, которую следует учитывать, - это то, что вы должны попытаться преобразовать свой UDF во встроенный TVF из-за некоторых побочных эффектов.

Как этот:

CREATE OR ALTER FUNCTION dvd_numb_left(@member_id INT)
RETURNS TABLE
AS
RETURN
(
WITH 
TM AS
(SELECT Membership.MembershipLimitPerMonth AS dvd_limit,
        Membership.DVDAtTime AS dvd_at_time,
        CONCAT(Member.MemberFirstName, ' ', Member.MemberLastName) AS [name]
 FROM   Membership AS MS
       JOIN Member AS M
             ON MS.MembershipId = M.MembershipId
 WHERE M.MemberId = @member_id
),
TR AS
(SELECT COUNT(Rental.MemberId) AS dvd_rented
 FROM   Rental
 WHERE  YEAR(Rental.RentalShippedDate ) = YEAR(GETDATE)
   AND  MONTH(Rental.RentalShippedDate ) = MONTH(GETDATE)
   AND  Rental.MemberId = @member_id
)
SELECT MemberId, [Name], 
       CASE WHEN dvd_limit - dvd_rented < 0 THEN 0 ELSE dvd_limit - dvd_rented END AS TotalDvdLeft,
       CASE WHEN dvd_limit - dvd_rented < 0  THEN 0 ELSE dvd_at_time - dvd_on_rent END AS AtTimeDvdLeft
FROM   TM CROSS JOIN TR
);
GO

Что будет намного эффективнее.

Абсолютное правило эффективности: ПОПЫТАЙТЕСЬ СОБЛЮДАТЬ КОД, ОСНОВАННЫЙ НА УСТАНОВКЕ, а не повторяющийся код.

Вышеупомянутая функция может быть оптимизирована оптимизатором, в то время как ваша не может и потребует 4 доступа к одним и тем же таблицам.

person SQLpro    schedule 13.04.2020