Sql Server Update trigger: how to make it work

Question

I am trying to write a sql server update trigger for my blockbuster movie company. In my MOVIES table, I have movie_id as PK, and another column called num_rented, which contains a total of how many times a movie is shot. This amount is done through input, deletion, and update triggers. (I understand that there are much better ways to do this, but my assignment specifically requires this, so please understand this). The CUSTOMER_RENTALS table has item_rental_id, since PK and movie_id from MOVIES are FK.

I need an update trigger that will update the num_rentals column in MOVIES whenever an update for CUSTOMER_RENTALS is done. For example, let's say that movie_id 1 was entered, but it was a mistake, and it was really movie_id 2. I would like num_rentals to reflect the update.

Here is what I still have, but I really don't know what to add to the SET part 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 think I need to access the values ​​of the deleted table in order to restore the num_rental column to its previous value, but I don't know how to do it. Thanks for the billion!

Answer 1

You need to consider that a DML statement can affect multiple rows (in this case, the INSERTED and DELETED tables will also have multiple rows.

You also need to consider that you must reduce the amount of rent for a movie if it is updated to a different identifier (and also increase the counter for a new movie).

Below, the counts from the inserts are combined and the net changes to the corresponding films are deleted and applied.

 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 believe you can do this by adding m.num_rentals + 1

Also add to the update statement for the remote

 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 prefer to create a view that the application can use to raise it. Thus, the removal of additional data processing must 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 

Answer 3

You need to execute the update trigger. You can try the following. For a better understanding of the trigger, I highly recommend this link 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