# Trigger Updating another table - Baseball Database

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

• Not sure you need bigint here. – paparazzo Sep 2 '18 at 19:11
• @paparazzo, I know it has been a bit of time, but could you expand on your thought about the BIGINTs? – Malachi May 1 '19 at 15:10

at first glance I found a couple of things that might give you issues

IF Strike = 1
BEGIN
SET @Strikes = 1;
SET @Balls = 0;
END
ELSE
BEGIN
SET @Strikes = 0;
SET @Balls = 1;
END


IIRC, you can perform 4 foul hits before you are considered "out", and that a foul hit on the first and second pitch are also counted as "Strikes" but not the third foul hit. going through this logic will give you a false "ball" count because it is a foul but not a strike.

I also noticed

IF Contact = 0
BEGIN
SET @Contact = 1;
END
ELSE
BEGIN
SET @Contact = 0;
END


I assumed that @Contact was your "hits" but I tried to go through the logic and I am a bit confused. If Contact = 0 means to me that there wasn't a hit, but you set the variable to true (SET @Contact = 1;)

Again IIRC, not every contact with the ball counts as a "hit"

• It looks like I misstyped that Contact part. Basically any time the batter makes contact with the ball is "contact", and in Baseball, you can foul off any number of pitches. So A foul and a fair ball would be considered contact. But it looks like I need to change that IF to be IF Contact = 1 – jDave1984 Nov 22 '17 at 20:36
• maybe the 4 Fouls and your out was a trick to keep games from getting too long in Tee-ball or Little League...lol I have always thought it was 4 fouls and your out, thanks for the info @jDave1984 – Malachi Mar 3 '20 at 18:29