2
\$\begingroup\$

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
\$\endgroup\$
2
  • \$\begingroup\$ Not sure you need bigint here. \$\endgroup\$ – paparazzo Sep 2 '18 at 19:11
  • \$\begingroup\$ @paparazzo, I know it has been a bit of time, but could you expand on your thought about the BIGINTs? \$\endgroup\$ – Malachi May 1 '19 at 15:10
1
\$\begingroup\$

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"

\$\endgroup\$
2
  • 1
    \$\begingroup\$ 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 \$\endgroup\$ – jDave1984 Nov 22 '17 at 20:36
  • \$\begingroup\$ 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 \$\endgroup\$ – Malachi Mar 3 '20 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.