sql - 根据用户定义函数的结果阻止更新列的触发器

Question

我们有 DVD 出租公司。在这个特定场景中，我们只考虑 Member、Rental 和 Membership 表。

任务是编写一个触发器，以防止客户根据使用该功能的会员合同达到其每月 DVD 租赁限额时收到 DVD。

我的触发器导致无限循环。它可以在没有 While 循环的情况下工作，但是如果我考虑对 Rental 表进行多次更新，它就不能正常工作。我哪里错了？

-- 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;

很高兴有任何建议。

标签： sqlsql-serverdatabase-trigger

Answer 1

您的主要问题是，即使您填充#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.