This is my first time writing T-SQL so I'd like to get some criticism on how I wrote this trigger.
I'm building a Pitch-Tracking App that will use a relational database to track Pitches, At-Bats, Games, etc. Everything starts with tracking the individual Pitch in an individual AB, so I figured whenever
tbl_PitchLog is updated, it would update that individual At Bat ID in the
tbl_AtBats table. Let me know if I'm straying or if I'm on some semblance of a straight path to elegant T-SQL
CREATE TABLE [dbo].[tbl_PitchLog] ( [Id] BIGINT NOT NULL PRIMARY KEY IDENTITY, [At_Bat_ID] BIGINT NOT NULL, [Batter_ID] BIGINT NOT NULL, [Pitcher_ID] BIGINT NOT NULL, [Game_ID] BIGINT NOT NULL, [Season_ID] BIGINT NOT NULL, [Pitch_Type] VARCHAR(50) NULL, [Pitch_Speed] BIGINT NULL, [Pitch_X] BIGINT NULL, [Pitch_Y] BIGINT NULL, [Strike] BIT NULL, [Swung] BIT NULL, [Contact] BIT NULL, [Fair] BIT NULL, [R] BIT NULL, [H] BIT NULL, [1B] BIT NULL, [2B] BIT NULL, [3B] BIT NULL, [HR] BIT NULL, [RBI] INT NULL, [BB] BIT NULL, [SO] BIT NULL, [IBB] BIT NULL, [HBP] BIT NULL, [SH] BIT NULL, [SF] BIT NULL, [GIDP] BIT NULL ) GO CREATE TRIGGER [dbo].[Trigger_tbl_PitchLog] ON [dbo].[tbl_PitchLog] FOR DELETE, INSERT, UPDATE AS DECLARE @Strikes integer, @Balls integer DECLARE @Fouls integer DECLARE @Contact integer IF Strike = 1 BEGIN SET @Strikes = 1; SET @Balls = 0; END ELSE BEGIN SET @Strikes = 0; SET @Balls = 1; END IF Fair = 0 BEGIN SET @Fouls = 1; END ELSE BEGIN SET @Fouls = 0; END IF Contact = 0 BEGIN SET @Contact = 1; END ELSE BEGIN SET @Contact = 0; END BEGIN SET NoCount ON UPDATE tbl_AtBats SET tbl_AtBats.PitchCount = tbl_AtBats.PitchCount + 1, tbl_AtBats.Strikes = tbl_AtBats.Strikes + @Strikes, tbl_AtBats.Balls = tbl_AtBats.Balls + @Balls, tbl_AtBats.Fouls = tbl_AtBats.Fouls + @Fouls, tbl_AtBats.Contact = tbl_AtBats.Contact + @Contact, tbl_AtBats.Swings = tbl_AtBats.Swings + inserted.Swung, tbl_AtBats.R = inserted.R, tbl_AtBats.H = inserted.H, tbl_AtBats.[1B] = inserted.[1B], tbl_AtBats.[2B] = inserted.[2B], tbl_AtBats.[3B] = inserted.[3B], tbl_AtBats.HR = inserted.HR, tbl_AtBats.RBI = inserted.RBI, tbl_AtBats.BB = inserted.BB, tbl_AtBats.SO = inserted.SO, tbl_AtBats.IBB = inserted.IBB, tbl_AtBats.HBP = inserted.HBP, tbl_AtBats.SH = inserted.SH, tbl_AtBats.SF = inserted.SF, tbl_AtBats.GIDP = inserted.GIDP WHERE tbl_AtBats.Id = inserted.At_Bat_ID; END