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