Databases
StackOverflow2013
Postsdbo
POMS SQL Server trigger to update item rating and number of votes
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMS SQL Server trigger to update item rating and number of votes
    text
    copied!<p><em>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)).</em></p> <p>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).</p> <p>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:</p> <pre><code>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 </code></pre> <p>but I get an error for the "GROUP BY" clause (which I expected). The question is, how can I make this work?</p> <p>Sorry if the question is stupid, but it's the first time I actually try to use triggers.</p> <p>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).</p> <p>The error message is "Incorrect syntax near the keyword 'GROUP'."</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload