Tuesday, April 10, 2012

creating triggers in order to implement ranking on a table

I have not ever worked on triggers in MSSQL 2008. SO I don't have any idea to do it.
I have a game application, I'm keeping track of the no of questions played & total no of questions answered correct. Also Im saving the average time taken for each question played.
Now I want to record the rank depending upon %age of answers correct and If there is a tie we will check the time taken. All I have to do this by using triggers.

  id   | playerID | CompetitionID  | NoOfCOrrectANswers | NoOfPlayedQuestions | TimeTaken 
   1      3             203              4                      8                  8.4
   2      56            203              9                       18                 13
   3      67            203              16                     45                   15

Any Help is appreciated. Thanks in advance.


You probably should not take this road as number of updates will be high. You might add row_number()or rank() function to a query that presents results of a competition.
That being said, if you feel that you absolutely must persist ranking info in a table, add a column
alter table ATable add PlayerRank int
and create a trigger on ATable
create trigger RankingTrigger on ATable
after insert, update, delete
-- We don't want queries in a trigger to mess up with "Rows affected"
   set nocount on
-- If any of the following columns are mentioned in a query
-- Always true for insert and delete, but we will save updates
-- if we skip processing when columns participating in ranking 
-- are not changed
   If update (NoOfCOrrectANswers)
      OR update(NoOfPlayedQuestions)
      OR update(TimeTaken)
      -- CTE that returns primary key and rank by competition.
      -- I've changed your condition (percent of correct answers)
      -- As it would rank players with one correct answer over those
      -- who answered 100 questions and got only one wrong. If you
      -- want to change it, replace NoOfCOrrectANswers with 
      -- NoOfCOrrectANswers / NoOfPlayedQuestions
      ; with theRank as (
        select ID, row_number() over (partition by CompetitionID
                                      order by NoOfCOrrectANswers DESC,
                                               TimeTaken DESC) rn
          from ATable
      -- Only changed competitions
         where CompetitionID in
      -- Inserted table is available in trigger and OUTPUT clause
      -- of insert, update, delete statements.
      -- It contains newly added/changed rows
      -- We are using it here to filter only changed competitions
      -- Similary, Deleted table hold a copy of removed rows for delete 
      -- and old values for update
            select CompetitionID
              from Inserted
            select CompetitionID
              from Deleted
      update ATable
      -- Update to rank from theRank CTE
         set PlayerRank = theRank.rn
        from ATable
        -- All records participation in affected competitions
          inner join theRank
             on ATable.ID = theRank.ID
        -- Only change if really changed
        -- This part is very likely not needed. I have never tested
        -- to see if it affects performance
            and isnull(ATable.PlayerRank, 0) <> theRank.rn

No comments:

Post a Comment