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
How can I best create a link between the
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