SQL Server触发器:不能绑定多部分标识符

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.

我正在尝试创建一个相当简单的触发器，将其添加到一个列中，该列跟踪类似于Netflix的电影发行公司的租借数量。

The columns I am focused on are:

我关注的专栏是:

Movies (movie_id, movie_title, release_year, num_rentals)
电影(movie_id, movie_title, release_year, num_rentals)
Customer_rentals (item_rental_id, movie_id, rental_date_out, rental_date_returned)
customer_rental_id (item_rental_id、movie_id、rental_date_out、rental_date_return)

My current trigger looks like this:

我现在的触发器是这样的:

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

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

Msg 4104，第16层，状态1，过程tr_num_rented_insert，第7行多部分标识符“customer_rentals”。movie_id”无法绑定。

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

我只是想让它匹配movie_id，并在租房数量上增加1。

2 个解决方案

#1

You need to join to the inserted pseudo-table:

您需要连接到已插入的伪表:

CREATE TRIGGER dbo.tr_num_rented_insert
ON dbo.customer_rentals 
FOR INSERT
AS
BEGIN 
  UPDATE m
    SET num_rentals = num_rentals + 1
  FROM dbo.movies AS m
  INNER JOIN inserted AS i
  ON m.movie_id = i.movie_id;
END
GO

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
WITH SCHEMABINDING
AS
  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).

这里的另一个好处是，您不必执行任何技巧来将movies表中的其他列转换为结果。它还确保即使删除了租金(您的触发器将继续愉快地将+1添加到计数)，数据也是正确的。

#2

-1

Your code is correct for a simple insert trigger. Assuming those columns do exist in both tables, your problem may be that you put a semicolon at the end of the WHERE statement. The semicolon should only be after the END statement.

对于简单的插入触发器，您的代码是正确的。假设这两个表中都存在这些列，那么问题可能是在WHERE语句的末尾添加了分号。分号应该只在结束语句之后。

#1