SQL Server triggers only fire once per statement, no matter how many rows are INSERTed or UPDATEd (or DELETEd).

Therefore, it's not safe to use variables to get column data (unless you use cursors, and nobody wants that).

CREATE TRIGGER dbo.tr_num_rented_update

ON dbo.customer_rentals

AFTER UPDATE

AS

SET NOCOUNT ON;

UPDATE m

SET num_rentals = num_rentals + new.num_rentals - old.num_rentals

FROM dbo.movies AS m

INNER JOIN (

SELECT movie_id, COUNT(*) AS num_rentals

FROM inserted

GROUP BY movie_id

) AS new ON

new.movie_id = m.movie_id

INNER JOIN (

SELECT movie_id, COUNT(*) AS num_rentals

FROM deleted

GROUP BY movie_id

) AS old ON

old.movie_id = m.movie_id

GO