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
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
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