Sql Server 更新触发器：如何使其工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16124861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Server Update trigger: How to make it work
提问by Steve
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.
我正在尝试为我的“Blockbuster-like”电影公司编写一个 sql server 更新触发器。在我的 MOVIES 表中，我将 movie_id 作为 PK 和另一列名为 num_rented 的列，该列保留了电影的总租借次数。这个总数是通过我的插入、删除和更新触发器完成的。（我知道有更好的方法可以做到这一点，但我的任务特别要求这样做，所以请理解这一点）。CUSTOMER_RENTALS 表将 item_rental_id 作为 PK，而来自 MOVIES 的 movie_id 是 FK。
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.
我需要一个更新触发器，每当对 CUSTOMER_RENTALS 进行更新时，它都会更新 MOVIES 中的 num_rentals 列。例如，假设输入了 movie_id 1 但这是一个错误，它实际上是 movie_id 2。我希望 num_rentals 反映更新。
Here is what I have so far but I really don't know what to put in the SET portion to make this happen:
这是我到目前为止所拥有的，但我真的不知道在 SET 部分放什么来实现这一点：
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;
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!
我在想以某种方式我需要访问已删除表的值以将 num_rental 列恢复到其以前的值，但我不知道如何。提前感谢十亿！
回答by Martin Smith
You need to take account that a DML statement can affect multiple rows (in which case the INSERTEDand DELETEDtables will also have multiple rows.
您需要考虑到 DML 语句可能会影响多行（在这种情况下INSERTED，DELETED表也将有多行。
You also need to take into account that you should decrement the number of rentals for a movie if it is updated to a different id (as well as incrementing the count for the new movie).
您还需要考虑到，如果电影更新为不同的 ID，您应该减少电影的租借次数（以及增加新电影的计数）。
The below consolidates the counts from the inserts and deletes and applies the net changes to the relevant movieids.
下面合并了插入和删除的计数，并将净更改应用于相关的电影 ID。
CREATE TRIGGER tr_num_rentals_update
ON customer_rentals
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF UPDATE(movie_id) /*If column not affected skip*/
BEGIN
WITH T1(Cnt, movie_id)
AS (SELECT COUNT(*),
movie_id
FROM inserted
GROUP BY movie_id
UNION ALL
SELECT -COUNT(*), /*negative for deletes*/
movie_id
FROM deleted
GROUP BY movie_id),
T2
AS (SELECT SUM(Cnt) AS NetCnt,
movie_id
FROM T1
GROUP BY movie_id)
UPDATE m
SET num_rentals = num_rentals + NetCnt
FROM movies AS m
INNER JOIN T2
ON m.movie_id = T2.movie_id;
END
END;
回答by Christer Backlund H?llesli
I belive you can accomplish this by adding m.num_rentals + 1
我相信您可以通过添加 m.num_rentals + 1 来完成此操作
Also also add in a update statement for deleted one
还为已删除的一个添加更新语句
UPDATE M
SET num_rentals = m.numrentals - 1
FROM
Movies M
INNER JOIN Deleted D ON
M.movie_id = D.Movie_ID
However instead of having this in triggers I would rather create a view the application can use to pick this up. Thus removing the extra data handling needed to be performed with each update,insert and delete
然而，我宁愿创建一个应用程序可以用来获取它的视图，而不是在触发器中使用它。从而消除了每次更新、插入和删除时需要执行的额外数据处理
CREATE VIEW MoviesVW AS
SELECT M.Movie_ID, COUNT(R.*) AS Num_Rental
FROM Movies M
LEFT JOIN customer_rentals R ON
R.movies_id = M.movies_ID
GROUP BY
M.Movie_ID
回答by Ankush Jain
You need to perform after update trigger. You may try the following. For better understanding of trigger i strongly recommond this link http://www.codeproject.com/Articles/25600/Triggers-Sql-Server
您需要在更新触发器后执行。您可以尝试以下方法。为了更好地理解触发器，我强烈推荐此链接 http://www.codeproject.com/Articles/25600/Triggers-Sql-Server
CREATE TRIGGER trgAfterUpdate ON [dbo].[customer_rentals]
FOR UPDATE
AS
declare @num_rentals int;
declare @movie_id int;
SELECT @movie_id =i.movie_id from inserted i;
SELECT @num_rentals =num_rentals from MOVIES where movie_id =@movie_id
SET @num_rentals =@num_rentals +1;
-- perform update here in movies table
UPDATE MOVIES SET num_rentals=@num_rentals WHERE movie_id =@movie_id
GO
