Tournament medals

Medals.sql

-- 1. Creaza tabelul FARA chk_medal_recipient
CREATE TABLE Tournament_Medals (
    Tournament_Medals_ID INT AUTO_INCREMENT,
    Tournament_ID        INT NOT NULL,
    Sport_ID             INT NOT NULL,
    Category_ID          INT NOT NULL,
    Team_ID              INT NULL,
    Player_ID            INT NULL,
    Medal_Type           ENUM('Gold','Silver','Bronze') NOT NULL,

    PRIMARY KEY (Tournament_Medals_ID),

    CONSTRAINT fk_medal_tournament FOREIGN KEY (Tournament_ID)
        REFERENCES Tournament(Tournament_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_medal_sport FOREIGN KEY (Sport_ID)
        REFERENCES Sport(Sport_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_medal_category FOREIGN KEY (Category_ID)
        REFERENCES Category(Category_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_medal_team FOREIGN KEY (Team_ID)
        REFERENCES Team(Team_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT fk_medal_player FOREIGN KEY (Player_ID)
        REFERENCES Player(Player_ID)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT uq_medal UNIQUE (Tournament_ID, Category_ID, Medal_Type)
);

-- 2. Trigger BEFORE INSERT
DELIMITER $$
CREATE TRIGGER trg_medal_recipient_insert
BEFORE INSERT ON Tournament_Medals
FOR EACH ROW
BEGIN
    IF NOT (
        (NEW.Team_ID IS NOT NULL AND NEW.Player_ID IS NULL)
        OR
        (NEW.Team_ID IS NULL AND NEW.Player_ID IS NOT NULL)
    ) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'O medalie trebuie sa apartina fie unui Team, fie unui Player - nu ambilor sau niciunuia.';
    END IF;
END$$
DELIMITER ;

-- 3. Trigger BEFORE UPDATE
DELIMITER $$
CREATE TRIGGER trg_medal_recipient_update
BEFORE UPDATE ON Tournament_Medals
FOR EACH ROW
BEGIN
    IF NOT (
        (NEW.Team_ID IS NOT NULL AND NEW.Player_ID IS NULL)
        OR
        (NEW.Team_ID IS NULL AND NEW.Player_ID IS NOT NULL)
    ) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'O medalie trebuie sa apartina fie unui Team, fie unui Player - nu ambilor sau niciunuia.';
    END IF;
END$$
DELIMITER ;