4

I'm building a database on Microsoft SQL Server to teach myself SQL. I am a big UFC fan so I'd like to build a database on UFC fighters.

I'm building two tables fight and fighter. The fighter table will have stats like weight, height, birthday, etc. as well as a column for wins, losses, draws, and NC (no contest). The fight table will have a winner_id column and a loser_id column as well as is_draw and is_nc columns.

How can I best create a link between the winner_id/loser_id columns from the fight table to the wins and losses columns for the fighter table?

There is one caveat. Usually MMA fighters will come to the UFC with experience in other organizations. So they will already have a record of wins and losses prior to the UFC. My database will only record UFC fights. So I can't have those previous wins and losses in the [fight] table. For that reason, I've decided to use triggers for now.

Question: is there a better way to do this other than triggers? I'm thinking setting the wins column = a base number + the number of times their fighter_id is listed in the winner_id column. But how would I do that? Would I need to use a view?

Here is the trigger I have created.

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
    -- Win
    DECLARE @winner_id int
    DECLARE @loser_id int
    DECLARE @is_draw bit
    DECLARE @is_nc bit
    SELECT 
        @winner_id = winner_id,
        @loser_id = loser_id,
        @is_draw = is_draw,
        @is_nc = is_nc
        FROM inserted

    --Wins
    UPDATE fighter
    SET wins = wins+1
    WHERE fighter_id = @winner_id

    --Loss
    UPDATE fighter
    SET losses = losses+1
    WHERE fighter_id = @loser_id

    --Draw
    UPDATE fighter
    SET draws = draws + 
        CASE
            WHEN @is_draw = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

    --NC
    UPDATE fighter
    SET nc = nc + 
        CASE
            WHEN @is_nc = 1
            THEN 1
            ELSE 0
        END
    WHERE fighter_id = @winner_id
    AND fighter_id = @loser_id

END
  • 5
    Take care with triggers, inserted or deleted can have more than one row. – McNets Dec 11 '16 at 21:25
  • 1
    As @mcNets states: inserted and deleted are virtual tables. These tables can have more than one row in them. You are using these tables as though they only have one row. That is an error. – TT. Dec 12 '16 at 11:19
  • The cleanest way to deal with the win loss for any particular fighter is to create a view using COUNT and GROUP BY. The calculation will always be correct and you don't have to deal with a whole other set of headaches that go with triggers. – Queue Mann Jan 2 '18 at 15:55
1

I would start by determining the stats at run-time. Only if that turns out to be too expensive, I would denormalize the statistics:

select f1.fighter_id
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.winnner_id then 1 end) as #wins
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.looser_id then 1 end) as #loss
     , count(case when f2.is_draw = 1 and f1.fighter_id in (f2.winner_id, f2.looser_id) then 1 end) as #draws
     , ...   
from fighter f1
join fights  f2
    on f1.fighter_id in (f2.winnner_id, f2.looser_id)
group by f1.fighter_id; 
| improve this answer | |
0

In your trigger there are some errors:

The first: Calculation of draws

You have write this:

--Draw
UPDATE fighter
SET draws = draws + 
    CASE
        WHEN @is_draw = 1
        THEN 1
        ELSE 0
    END
WHERE fighter_id = @winner_id
AND fighter_id = @loser_id

But this query can't update anything because @winner_id and @loser_id are always different. You must change AND with OR

The second: Calculation of nc

the same error in draws repeated here, the solution is the same

The third: Are you sure your trigger process only one row?

In INSERTED table you have all rows involved in a INSERT or UPDATE statement.

If you have only a statement as follow:

INSERT INTO yourtable (field list) VALUES (list of values)

You have involved only one row and your trigger it's OK but, if you have a query as follow:

INSERT INTO yourtable
SELECT * FROM anothertable
WHERE conditions

If SELECT * FROM returns more than one row, your trigger works fine only for the first of those rows.

In this case you must deeply change your trigger code, a possible solution in this case is use an UPDATE ... FROM operation

| improve this answer | |
-1

When it comes to trigger, for anything but a check, use caution. Some things in life are "wrong way go back" and triggers are definitively one of them! Since what you need is a calculated column, I would say best go with a "View". As you've said:

wins column = a base number + the number of times their fighter_id is listed in the winner_id column

View are more flexible, easier to spot and easier to maintain. Also, the trigger should insert a record twice per game: one for the loser and one for the winner. Too much work. A view like the one below could do better:

The code would look like this:

 SELECT COUNT (winner_id),  winner_id AS Fighter,  'win' as ResType 
 FROM Results 
 WHERE is_draw = 0 
 GROUP BY winner_id 
 UNION ALL 
 SELECT COUNT (*),  loser_id as Fighter 'loss' as ResType 
 FROM Results 
 WHERE is_draw = 0 
 GROUP BY loser_id 
 UNION ALL 
 SELECT SUM(CAST is_draw as INT)  winner_id,  "draw" as res_type 
 FROM results 
 GROUP BY winner_id

Now you have all the wins, loses grouped by the fighter_id.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.