Sql Server Update trigger: How to make it work

Question

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.

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.

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

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!

Answer 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.

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).

The below consolidates the counts from the inserts and deletes and applies the net changes to the relevant 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; 

Answer 2

I belive you can accomplish this by adding 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