j r j r - 2 months ago 5x
SQL Question

SQL Server trigger: multi-part identifier could not be bound

I'm trying to create a fairly simple trigger that would add one to a column that keeps track of the number of rentals from a movie distribution company similar to Netflix.

The columns I am focused on are:

  • Movies (
    movie_id, movie_title, release_year, num_rentals

  • Customer_rentals (
    item_rental_id, movie_id, rental_date_out, rental_date_returned

My current trigger looks like this:

CREATE TRIGGER tr_num_rented_insert
ON customer_rentals FOR INSERT
UPDATE movies
SET num_rentals=num_rentals+1
WHERE customer_rentals.movie_id=movies.movie_id;

It returns the error:

Msg 4104, Level 16, State 1, Procedure tr_num_rented_insert, Line 7

The multi-part identifier "customer_rentals.movie_id" could not be

I just want it to match the movie_id's and add 1 to the number of rentals.


You need to join to the inserted pseudo-table:

CREATE TRIGGER dbo.tr_num_rented_insert
ON dbo.customer_rentals 
    SET num_rentals = num_rentals + 1
  FROM dbo.movies AS m
  INNER JOIN inserted AS i
  ON m.movie_id = i.movie_id;

But I have to ask, what is the point of keeping this count up to date in the movies table? You can always get the count in a query instead of storing it redundantly:

SELECT m.movie_id, COALESCE(COUNT(r.movie_id))
  FROM dbo.moves AS m
  LEFT OUTER JOIN dbo.customer_rentals AS r
  ON m.movie_id = r.movie_id
  GROUP BY m.movie_id;

And if performance of that query becomes an issue, you can create an indexed view to maintain the count so that you don't have to keep it up to date with a trigger:

CREATE VIEW dbo.rental_counts
  SELECT movie_id, num_rentals = COUNT_BIG(*)
  FROM dbo.customer_rentals
  GROUP BY movie_id;

This causes the same kind of maintenance as your trigger, but does it without your trigger, and does it without affecting the movies table. Now to get the rental counts you can just say:

SELECT m.movie_id, m.other_columns, 
    num_rentals = COALESCE(r.num_rentals, 0)
  FROM dbo.movies AS m
  LEFT OUTER JOIN dbo.rental_counts AS r
  ON m.movie_id = r.movie_id;

(We use a LEFT JOIN here because a movie may not have been rented yet.)

An additional bonus here is that you don't have to perform any tricks to get other columns from the movies table into the result. It also ensures that the data is accurate even if a rental is deleted (your trigger will continue to happily add +1 to the count).