Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First, I strongly recommend that you <em>not</em> use triggers. </p> <p>If you're getting a syntax error, check that your parens are balanced as well as your <code>begin</code>/<code>ends</code>. In your case, you have an <code>end</code> (at the end) but no begin. You can fix that be just removing the <code>end</code>. </p> <p>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.</p> <p>This is all assuming that there are multiple records with the same threadID (ie, it's not the primary key). If that's <em>not</em> the case, then what's the purpose of the group by?</p> <hr> <p>Edit:</p> <p>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:</p> <pre><code>--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 </code></pre> <p>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.</p> <hr> <p><strong>Alternatives to triggers</strong></p> <p>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.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. POMS SQL Server trigger to update item rating and number of votes
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMichael Haren
    1. USMichael Haren
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMS SQL Server trigger to update item rating and number of votes
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThere could be multiple rows with the same threadid in the threadrating table, not in the thread table. I expected the same error you mention, but I am getting a more humiliating "Incorrect syntax near the keyword 'GROUP'". If you don't recommend triggers, what would you recommend for such a case?
      singulars
      1. PO
      1. USLea Verou
    2. CORemove the "End" keyword at the end. (or add the word 'begin' after 'as')
      singulars
      1. PO
      1. USMichael Haren
    3. COI haven't seen the edit previously. Nope, the threadrating table only has 3 fields: threadid, userid, rating. Can you elaborate on the performance part? I decided upon that whole concept for performance reasons...
      singulars
      1. PO
      1. USLea Verou
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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