MS SQL Server launches update of position rating and number of votes

Question

To make this clear, I’ll talk about the same problem as if it was a forum (the actual application is not connected to the forums at all, but I think that such a parallel is easier for most, we understand that the actual application is related to something very specific that most programmers will not understand (this application is intended for hardcore graphic designers)).

Suppose there is a thread table that stores information about each thread in the forum and a thread table that stores thread ratings for the user (1-5). For efficiency, I decided to remove the average rating and the number of votes in the thread table, and the triggers sounded like a good idea for updating it (I used such things in the actual application code, but I think that triggers are worth a try, despite debugging threats).

As you know, MS SQL Server does not support triggering a trigger for each row; this should be a statement. So I tried to define it as follows:

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 do this work?

Sorry if the question is stupid, but this is the first time I'm actually trying to use triggers.

Additional information: The table of threads will contain threadid (int, primary key), rating (float), voters (int) and some other fields that are not relevant to the current issue. The thread table contains only the threadid file (foreign key), userid (foreign key for the primary key of the user table) and rating (tinyint from 1 to 5).

Error message "Invalid syntax next to the keyword" GROUP "."

Answer 1

Firstly, I highly recommend not using triggers.

If you get a syntax error, make sure your partners are balanced, as well as your begin / ends . In your case, you have end (at the end), but don't start. You can fix this by simply removing end .

As soon as you fix this, you will most likely get some more errors, such as "columns x, y, z are not in the aggregate or group." This is because you have multiple columns that are not in any. You need to add thread.rating, thread.voters, etc. To your group or perform some kind of aggregate on them.

All this assumes that there are several records with the same stream identifier (i.e. this is not a primary key). If this is not so, then what is the purpose of the group :?

---

Edit:

I have a strong syntax error. I worked around it with a pair of correlated subqueries. I guessed about your table structure, so change it as needed and try the following:

 --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 this is what you wanted, we can check the performance / implementation plan and change it if necessary. Perhaps we can get him to work with the group.

---

Alternatives to triggers

If you are updating data that affects ratings in only a few selected places, I would recommend updating ratings right there. The factoring of logic in a trigger is good, but it provides many problems (performance, visibility, etc.). This can help the function.

Consider this: your trigger will execute every time someone touches this table. Actions such as counting views, last updated dates, etc., will trigger this trigger. You can add logic to trigger a short circuit in these cases, but it gets complicated quickly.

Answer 2

D'ooo! I completely misunderstood your question, and I thought you were asking about MySQL. My fault! I will leave the solution intact and mark it as a wiki community. Perhaps this will be useful for someone with a similar problem in MySQL.

---

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

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

Here is the 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 

Similarly, you will 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 can find the following helpful tips:

Introduction to Triggers
Wikipedia: DB triggers