# Win-Loss Record in SQL Server

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.

Here is the trigger I have created. My question is, 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?

CREATE TRIGGER tr_fight_ResultUpdateFighter
ON fight
FOR INSERT
AS
BEGIN
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

• That is poor design. Weight should be in the fight table as you can fight as different weight. A simple view can sum up wins and losses. This is wrong WHERE fighter_id = _winner_id AND fighter_id = _loser_id. This is not working code. I think it would be a better fit on dba.stackexchange.com. – paparazzo Dec 11 '16 at 19:20
• Don't cross post dba.stackexchange.com/questions/157830/… – paparazzo Dec 12 '16 at 7:33
• Dear, DFraw and NC are worng. you must use OR instead of AND – Joe Taras Jan 20 '17 at 9:21