MS SQL Server trigger to update item rating and number of votes

Question

To make this easier to understand, I will present the exact same problem as if it was about a forum (the actual app doesn't have to do with forums at all, but I think such a parallel is easier for most of us to grasp, the actual app is about something very specific that most programmers won't understand (it's an app intended for hardcore graphic designers)).

Let's suppose that there is a thread table that stores information about each forum thread and a threadrating table that stores thread ratings per user (1-5). For efficiency I decided to cache the rating average and number of votes in the thread table and triggers sounded like a good idea for updating it (I used to do such stuff in the actual application code, but I think triggers are worth a try, despite the debugging dangers).

As you know, MS SQL Server doesn't support a trigger to be executed per row, it has to be per statement. So I tried defining it this way:

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

but I get an error for the "GROUP BY" clause (which I expected). The question is, how can I make this work?

Sorry if the question is stupid, but it's the first time I actually try to use triggers.

Additional info: The thread table would contain threadid (int, primary key), rating (float), voters(int) and some other fields that are irrelevent to the current question. The threadrating table only contains threadid (foreign key), userid (foreign key to the primary key of the users table) and rating (tinyint between 1 and 5).

The error message is "Incorrect syntax near the keyword 'GROUP'."

Answer 1

First, I strongly recommend that you not use triggers.

If you're getting a syntax error, check that your parens are balanced as well as your begin/ends. In your case, you have an end (at the end) but no begin. You can fix that be just removing the end.

Once you fix that, you'll likely get some more errors like "columns x,y,z not in an aggregate or group by". That's because you have several columns that are not in either. You need to add thread.rating, thread.voters, etc. to your group by or perform some kind of aggregate on them.

This is all assuming that there are multiple records with the same threadID (ie, it's not the primary key). If that's not the case, then what's the purpose of the group by?

---

Edit:

I'm stumped on the syntax error. I worked around it with a couple correlated sub queries. I guessed at your table structure so modify as needed and try this:

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

If that's what you wanted, then we can check the performance/execution plan and modify as needed. We might be able to get it to work with the group by yet.

---

Alternatives to triggers

If you are updating data that impact ratings in only a few select places, I'd recommend updating the ratings directly there. Factoring the logic into a trigger is nice but provides lots of problems (performance, visibility, etc.). This can be aided by a function.

Consider this: your trigger will execute every single time someone touches that table. Things like view counts, last updated dates, etc. will execute this trigger. You can add logic to short circuit the trigger in those cases but it gets complicated rapidly.

Answer 2

D'ohh! I totally misread your question and I thought you were asking about MySQL. Mea culpa! I will leave the solution below intact, and mark it as community wiki. Maybe it'll be useful to someone with a similar problem on MySQL.

---

MySQL triggers are executed per row. Also the pseudo-table "inserted" is a Microsoft SQL Server convention.

MySQL uses pseudo-tables NEW and OLD as extensions to the trigger language.

Here's a solution to your problem:

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

Likewise you'd need triggers for UPDATE and 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

Answer 3

You may find the following reading helpful:

An introduction to Triggers
Wikipedia: DB Triggers