sql-server - 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，主键)，评级(float)，voters(int)和一些与当前问题无关的其他字段。thread rating表只包含threadid(外键)、userid(users表主键的外键)和 rating(1到5之间的tinyint)。

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

Answer 1

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

如果出现语法错误，请检查括号以及开始/结束是否平衡。就您而言，您有一个 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

如果这就是您想要的，那么我们可以检查性能/执行计划并根据需要进行修改。我们也许可以让它与团队一起工作。

<小时/>

触发器的替代方案

如果您更新的数据仅影响少数选定位置的评分，我建议您直接在那里更新评分。将逻辑分解到触发器中很好，但会带来很多问题(性能、可见性等)。这可以通过函数来​​帮助。

考虑一下:每次有人触摸该表时，您的触发器都会执行。浏览次数、上次更新日期等都会执行此触发器。在这些情况下，您可以添加逻辑来短路触发器，但它很快就会变得复杂。