SQL Server触发器：不能绑定多部分标识符 [英] SQL Server trigger: multi-part identifier could not be bound

问题描述

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

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.

我关注的列是：

• 电影（ movie_id，movie_title ，release_year，num_rentals ）


• Customer_rentals （ item_rental_id，movie_id，rental_date_out，rental_date_returned ）

• Movies (movie_id, movie_title, release_year, num_rentals)
• Customer_rentals (item_rental_id, movie_id, rental_date_out, rental_date_returned)

我当前的触发器如下：

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;

它返回错误：

消息4104，级别16，状态1，过程tr_num_rented_insert，第7行

多部分标识符 customer_rentals.movi​​e_id不能与
绑定。

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.

我只希望它与movie_id匹配，并将租金增加1。

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

推荐答案

您需要加入插入的伪表：

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;

（我们在这里使用LEFT JOIN，因为可能尚未租借电影。）

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

这里的另一个好处是，您不必执行任何技巧即可将movies表中的其他列添加到结果中。即使删除了租金，它也可以确保数据准确无误（触发器将继续愉快地为计数增加+1）。

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

这篇关于SQL Server触发器：不能绑定多部分标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！