Sql Server Update触发器：如何使其工作

I am trying to write a sql server update trigger for my "Blockbuster-like" movie company. In my MOVIES table I have movie_id as the PK and another column called num_rented that keeps a total of how many times a movie is rented. This total is done through my insert, delete and update triggers. (I understand that there are far better ways to do this but my assignment specifically calls for this so please understand this point). The CUSTOMER_RENTALS table has item_rental_id as the PK and movie_id from MOVIES is a FK.

我正在尝试为我的“Blockbuster-like”电影公司编写一个sql server更新触发器。在我的MOVIES表中,我将movie_id作为PK,另一列名为num_rented,它总共保留了一部电影的租借次数。这个总数是通过我的插入,删除和更新触发器完成的。 (我知道有更好的方法可以做到这一点,但我的任务特别要求这样做,所以请理解这一点)。 CUSTOMER_RENTALS表具有item_rental_id作为PK,MOVIES中的movie_id是FK。

I need an update trigger that will update the num_rentals column in MOVIES whenever an update is made to CUSTOMER_RENTALS. For example, say movie_id 1 was input but that was an error and it was really movie_id 2. I would want the num_rentals to reflect the update.

我需要一个更新触发器,只要对CUSTOMER_RENTALS进行更新,它就会更新MOVIES中的num_rentals列。例如,假设movie_id 1已输入,但这是一个错误,它实际上是movie_id 2.我希望num_rentals反映更新。

Here is what I have so far but I really don't know what to put in the SET portion to make this happen:

这是我到目前为止所做的,但我真的不知道在SET部分放置什么来实现这一点:

CREATE TRIGGER tr_num_rentals_update
ON customer_rentals
AFTER UPDATE
AS
BEGIN
UPDATE m
SET num_rentals = ??????
FROM movies AS m
INNER JOIN inserted as i on m.movie_id=i.movie_id;
END;

I am thinking somehow I need to access the deleted table's values to restore the num_rental column to its previous value but I don't know how. Thanks a billion in advance!

我想某种方式我需要访问已删除的表的值,以将num_rental列恢复为以前的值,但我不知道如何。提前感谢十亿!

3 个解决方案

#1

You need to take account that a DML statement can affect multiple rows (in which case the INSERTED and DELETED tables will also have multiple rows.

您需要考虑DML语句可能影响多行(在这种情况下,INSERTED和DELETED表也将有多行。

You also need to take into account that you should decrement the number of rentals for a movie if it is updated to a different id (as well as incrementing the count for the new movie).

您还需要考虑到,如果电影更新为不同的ID(以及增加新电影的计数),则应减少电影的租借数量。

The below consolidates the counts from the inserts and deletes and applies the net changes to the relevant movieids.

下面合并插入和删除的计数,并将净更改应用于相关的movieids。

CREATE TRIGGER tr_num_rentals_update
ON customer_rentals
AFTER INSERT, UPDATE, DELETE
AS
  BEGIN
      IF UPDATE(movie_id) /*If column not affected skip*/
        BEGIN
            WITH T1(Cnt, movie_id)
                 AS (SELECT COUNT(*),
                            movie_id
                     FROM   inserted
                     GROUP  BY movie_id
                     UNION ALL
                     SELECT -COUNT(*), /*negative for deletes*/
                            movie_id
                     FROM   deleted
                     GROUP  BY movie_id),
                 T2
                 AS (SELECT SUM(Cnt) AS NetCnt,
                            movie_id
                     FROM   T1
                     GROUP  BY movie_id)
            UPDATE m
            SET    num_rentals = num_rentals + NetCnt
            FROM   movies AS m
                   INNER JOIN T2
                     ON m.movie_id = T2.movie_id;
        END
  END;

#2

I belive you can accomplish this by adding m.num_rentals + 1

我相信你可以通过添加m.num_rentals + 1来实现这一点

Also also add in a update statement for deleted one

还要为已删除的一个添加更新语句

UPDATE M
SET num_rentals = m.numrentals - 1
FROM
Movies M
INNER JOIN Deleted D ON
M.movie_id = D.Movie_ID

However instead of having this in triggers I would rather create a view the application can use to pick this up. Thus removing the extra data handling needed to be performed with each update,insert and delete

但是,我宁愿创建一个应用程序可以用来选择它的视图,而不是在触发器中使用它。因此,删除每次更新,插入和删除时需要执行的额外数据处理

CREATE VIEW MoviesVW AS
SELECT M.Movie_ID, COUNT(R.*) AS Num_Rental
FROM Movies M
LEFT JOIN customer_rentals R ON
R.movies_id = M.movies_ID
GROUP BY
M.Movie_ID

#3

-1

You need to perform after update trigger. You may try the following. For better understanding of trigger i strongly recommond this link http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

您需要在更新触发器后执行。您可以尝试以下方法。为了更好地理解触发器,我强烈推荐这个链接http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

CREATE TRIGGER trgAfterUpdate ON [dbo].[customer_rentals] 
FOR UPDATE
AS
    declare @num_rentals int;
    declare @movie_id int;

    SELECT @movie_id =i.movie_id from inserted i;   
    SELECT @num_rentals =num_rentals from MOVIES where movie_id =@movie_id 
    SET @num_rentals =@num_rentals +1;

      -- perform update here in movies table    

    UPDATE MOVIES SET num_rentals=@num_rentals WHERE movie_id =@movie_id 

GO

#1