MS SQL Server触发器更新项目评分和票数

Question

为了更容易理解，我将呈现完全相同的问题，就好像它是关于论坛一样（实际应用程序与论坛无关所有，但我认为这样的并行对我们大多数人来说更容易掌握，实际的应用程序是关于大多数程序员不会理解的非常具体的东西（它是针对硬核图形设计者的应用程序））。

让我们假设有一个线程表存储有关每个论坛线程和一个存储每个用户（1-5）的线程评级的线程表的信息。为了提高效率，我决定缓存线程表中的评分平均值和投票数，触发器听起来像是更新它的好主意（我曾经在实际的应用程序代码中做过这样的事情，但我认为触发器值得一试，尽管调试危险）。如您所知，MS SQL Server不支持每行要执行的触发器，它必须是每个语句。所以我试图用这种方式来定义它：

CREATE TRIGGER thread_rating ON threadrating 
AFTER INSERT 
AS 
    UPDATE thread 
    SET 
     thread.rating = (thread.rating * thread.voters + SUM(inserted.rating))/(thread.voters + COUNT(inserted.rating)), 
     thread.voters = thread.voters + COUNT(inserted.rating) 
    FROM thread 
    INNER JOIN inserted ON(inserted.threadid = thread.threadid) 
    GROUP BY inserted.threadid 

但是我得到了一个“GROUP BY”子句（我期望的）的错误。问题是，我该如何做这项工作？

对不起，如果问题很愚蠢，但这是我第一次尝试使用触发器。

附加信息：线程表将包含threadid（int，主键），rating（float），vot（int）以及其他与当前问题无关的字段。 线程表只包含threadid（外键），userid（用户表的主键的外键）和rating（tinyint在1和5之间）。

错误消息是“关键字'GROUP'附近的语法不正确。”

Answer 1

首先，我强烈建议您不使用触发器。

如果您收到语法错误，请检查您的伙伴平衡以及您的begin/ends。在你的情况下，你有一个end（最后），但没有开始。你可以修复那只是删除end。

一旦你解决了这个问题，你可能会得到更多的错误，比如“列x，y，z不在聚合或组合中”。那是因为你有几列不在其中。您需要将thread.rating，thread.voters等添加到您的组中，或者对它们执行某种聚合。

这是假设有多个记录具有相同的threadID（即它不是主键）。如果那不是的情况下，那么这个小组的目的是什么？

---

编辑：

我难倒了语法错误。我用一些相关的子查询来解决它。我在你的表结构猜到所以根据需要修改和尝试这个办法：

--CREATE TABLE ThreadRating (threadid int not null, userid int not null, rating int not null) 
--CREATE TABLE Thread (threadid int not null, rating int not null, voters int not null) 

ALTER TRIGGER thread_rating ON threadrating 
AFTER INSERT 
AS 

UPDATE Thread 
SET Thread.rating = 
    (SELECT (Thread.Rating * Thread.Voters + SUM(I.Rating))/(Thread.Voters + COUNT(I.Rating)) 
    FROM ThreadRating I WHERE I.ThreadID = thread.ThreadID) 
    ,Thread.Voters = 
    (SELECT Thread.Voters + COUNT(I.Rating) 
    FROM ThreadRating I WHERE I.ThreadID = Thread.ThreadID)       
FROM Thread 
JOIN Inserted ON Inserted.ThreadID = Thread.ThreadID 

如果这是你想要的东西，那么我们可以检查性能/执行计划，并根据需要修改。我们或许可以通过它与团队合作。

---

替代触发器

如果要更新，在只有少数选择地影响收视率，我建议你更新的收视率有直接的数据。将逻辑分解为触发器很不错，但会带来很多问题（性能，可视性等）。这可以通过函数来​​帮助。

考虑一下：触发器会在每次有人触摸该表时执行。像查看计数，上次更新的日期等将执行此触发器。在这些情况下，您可以添加逻辑来短接触发器，但它很快变得复杂。

Answer 2

您可能会发现下面的阅读有帮助：

An introduction to Triggers
Wikipedia: DB Triggers

Answer 3

D'ohh！我完全误解了你的问题，我以为你在问MySQL。 Mea culpa！我将保留下面的解决方案，并将其标记为社区wiki。也许这对MySQL上类似问题的人会有用。

---

MySQL的触发是每行执行。此外，伪表“inserted”是Microsoft SQL Server约定。

MySQL使用伪表NEW和OLD作为trigger language的扩展。

这里是你的问题的解决方案：

CREATE TRIGGER thread_rating 
    AFTER INSERT ON threadrating 
    FOR EACH ROW 
BEGIN 
    UPDATE thread 
    SET rating = (rating*voters + NEW.rating)/(voters+1), 
     voters = voters + 1 
    WHERE threadid = NEW.threadid; 
END 

同样你需要触发的UPDATE和DELETE：

CREATE TRIGGER thread_rating 
    AFTER UPDATE ON threadrating 
    FOR EACH ROW 
BEGIN 
    UPDATE thread 
    SET rating = (rating*voters - OLD.rating + NEW.rating)/voters, 
    WHERE threadid = NEW.threadid; 
END 

CREATE TRIGGER thread_rating 
    AFTER DELETE ON threadrating 
    FOR EACH ROW 
BEGIN 
    UPDATE thread 
    SET rating = (rating*voters - OLD.rating)/(voters-1), 
     voters = voters - 1 
    WHERE threadid = OLD.threadid; 
END