title: Sql Server Update trigger: How to make it work
body: <p>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.  </p>
  
  <p>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.  </p>
  
  <p>Here is what I have so far but I really don't know what to put in the SET portion to make this happen:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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!  </p>