Only 'F' can be assigned Athletics_100m_Hurdles

gistfile1.txt

DELIMITER $$

CREATE TRIGGER trg_athlete_hurdles_gender_check
BEFORE INSERT ON Athlete_Profile
FOR EACH ROW
BEGIN
    DECLARE v_gender ENUM('M', 'F');

    -- Retrieve the player's gender from the Player supertype
    SELECT Gender
    INTO v_gender
    FROM Player
    WHERE Player_ID = NEW.Player_ID;

    -- Only 'F' can be assigned Athletics_100m_Hurdles
    IF NEW.Athlete_Preferred_Category = 'Athletics_100m_Hurdles'
        AND v_gender != 'F' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT =
            'Gender violation: Athletics_100m_Hurdles is restricted to female athletes';
    END IF;

    -- Only 'M' can be assigned Athletics_110m_Hurdles
    IF NEW.Athlete_Preferred_Category = 'Athletics_110m_Hurdles'
        AND v_gender != 'M' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT =
            'Gender violation: Athletics_110m_Hurdles is restricted to male athletes';
    END IF;

END $$

DELIMITER ;


DELIMITER $$

CREATE TRIGGER trg_athlete_hurdles_gender_check_update
BEFORE UPDATE ON Athlete_Profile
FOR EACH ROW
BEGIN
    DECLARE v_gender ENUM('M', 'F');

    -- Retrieve the player's gender from the Player supertype
    SELECT Gender
    INTO v_gender
    FROM Player
    WHERE Player_ID = NEW.Player_ID;

    -- Only 'F' can be assigned Athletics_100m_Hurdles
    IF NEW.Athlete_Preferred_Category = 'Athletics_100m_Hurdles'
        AND v_gender != 'F' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT =
            'Gender violation: Athletics_100m_Hurdles is restricted to female athletes';
    END IF;

    -- Only 'M' can be assigned Athletics_110m_Hurdles
    IF NEW.Athlete_Preferred_Category = 'Athletics_110m_Hurdles'
        AND v_gender != 'M' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT =
            'Gender violation: Athletics_110m_Hurdles is restricted to male athletes';
    END IF;

END $$

DELIMITER ;