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
