CREATE OR REPLACE FUNCTION upd_partners_data() RETURNS TRIGGER AS $partner$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE partner SET couple_id = NULL WHERE id = OLD.partner1_id;
            UPDATE partner SET couple_id = NULL WHERE id = OLD.partner2_id;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
            UPDATE partner SET name = 'partner1', level = NEW.level, club_id = NEW.club_id, couple_id = NEW.id WHERE id = NEW.partner1_id;
            UPDATE partner SET name = 'partner2', level = NEW.level, club_id = NEW.club_id, couple_id = NEW.id WHERE id = NEW.partner2_id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$partner$ LANGUAGE plpgsql;


CREATE TRIGGER partner
AFTER INSERT OR UPDATE OR DELETE ON couples
    FOR EACH ROW EXECUTE PROCEDURE upd_partners_data();




CREATE OR REPLACE FUNCTION upd_clubs_data() RETURNS TRIGGER AS $couples$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE clubs SET couples_num = (SELECT couples_num FROM clubs WHERE id = OLD.club_id) - 1,
                couple_ids = array_remove(couple_ids, OLD.id)  WHERE id = OLD.club_id;
            DELETE FROM global_raiting WHERE couple_id = OLD.id;
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE clubs SET couples_num = (SELECT couples_num FROM clubs WHERE id = NEW.club_id) + 1,
                couple_ids = array_append(couple_ids, NEW.id) WHERE id = NEW.club_id;
            INSERT INTO global_raiting (couple_id, club_id, score, level) VALUES (NEW.id, NEW.club_id, 0, NEW.level);
            RETURN NEW;
         ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE clubs SET couples_num = (SELECT couples_num FROM clubs WHERE id = NEW.club_id) + 1,
                couple_ids = array_append(couple_ids, NEW.id) WHERE id = NEW.club_id;
            UPDATE clubs SET couples_num = (SELECT couples_num FROM clubs WHERE id = OLD.club_id) - 1,
                couple_ids = array_remove(couple_ids, OLD.id) WHERE id = OLD.club_id;
            UPDATE global_raiting SET club_id = NEW.club_id WHERE couple_id = NEW.id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$couples$ LANGUAGE plpgsql;


CREATE TRIGGER couple_club_upd
AFTER INSERT OR UPDATE OR DELETE ON couples
    FOR EACH ROW EXECUTE PROCEDURE upd_clubs_data();


CREATE OR REPLACE FUNCTION upd_trainer_data_for_clubs() RETURNS TRIGGER AS $trainers$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE clubs SET trainer_ids = array_remove(trainer_ids, OLD.id)  WHERE id = OLD.club_id;
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE clubs SET trainer_ids = array_append(trainer_ids, NEW.id)  WHERE id = NEW.club_id;
            RETURN NEW;
         ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE clubs SET trainer_ids = array_append(trainer_ids, NEW.id) WHERE id = NEW.club_id;
            UPDATE clubs SET trainer_ids = array_remove(trainer_ids, OLD.id) WHERE id = OLD.club_id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$trainers$ LANGUAGE plpgsql;


CREATE TRIGGER upd_trainer_data_for_clubs
AFTER INSERT OR UPDATE OR DELETE ON trainers
    FOR EACH ROW EXECUTE PROCEDURE upd_trainer_data_for_clubs();



CREATE OR REPLACE FUNCTION upd_shedule_data_for_comps() RETURNS TRIGGER AS $competition_groups_shedule$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE competitions_shedule SET group_shedule_ids = array_remove(group_shedule_ids, OLD.id)  WHERE id = OLD.comp_id;
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE competitions_shedule SET group_shedule_ids = array_append(group_shedule_ids, NEW.id)  WHERE id = NEW.comp_id;
            RETURN NEW;
         ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE competitions_shedule SET group_shedule_ids = array_append(group_shedule_ids, NEW.id) WHERE id = NEW.comp_id;
            UPDATE competitions_shedule SET group_shedule_ids = array_remove(group_shedule_ids, OLD.id) WHERE id = OLD.comp_id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$competition_groups_shedule$ LANGUAGE plpgsql;

CREATE TRIGGER upd_shedule_data_for_comps
AFTER INSERT OR UPDATE OR DELETE ON competition_groups_shedule
    FOR EACH ROW EXECUTE PROCEDURE upd_shedule_data_for_comps();



CREATE OR REPLACE FUNCTION upd_results_data_for_comps() RETURNS TRIGGER AS $competition_groups_results$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE competitions_shedule SET group_results_ids = array_remove(group_results_ids, OLD.id)  WHERE id = OLD.comp_id;
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE competitions_shedule SET group_results_ids = array_append(group_results_ids, NEW.id)  WHERE id = NEW.comp_id;
            RETURN NEW;
         ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE competitions_shedule SET group_results_ids = array_append(group_results_ids, NEW.id) WHERE id = NEW.comp_id;
            UPDATE competitions_shedule SET group_results_ids = array_remove(group_results_ids, OLD.id) WHERE id = OLD.comp_id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$competition_groups_results$ LANGUAGE plpgsql;

CREATE TRIGGER upd_results_data_for_comps
AFTER INSERT OR UPDATE OR DELETE ON competition_groups_results
    FOR EACH ROW EXECUTE PROCEDURE upd_results_data_for_comps();


CREATE OR REPLACE FUNCTION upd_result_data_for_comp_results() RETURNS TRIGGER AS $global_comp_group_result_table$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            UPDATE competition_groups_results SET group_results_ids = array_remove(group_results_ids, OLD.id)  WHERE id = OLD.group_results_id;
            RETURN OLD;
        ELSIF (TG_OP = 'INSERT') THEN
            UPDATE competition_groups_results SET group_results_ids = array_append(group_results_ids, NEW.id)  WHERE id = NEW.group_results_id;
            IF (EXISTS (SELECT 1 FROM global_raiting WHERE couple_id = NEW.couple_id)) THEN
                UPDATE global_raiting SET score = (SELECT score FROM global_raiting WHERE couple_id = NEW.couple_id) + NEW.score WHERE couple_id = NEW.couple_id; -- проверить скор на нулл?
            ELSE
                INSERT INTO global_raiting (couple_id, club_id, score, level) VALUES (NEW.couple_id,
                    (SELECT club_id FROM couples WHERE id = NEW.couple_id),
                    NEW.score,
                    (SELECT level FROM couples WHERE id = NEW.couple_id)
                    );
            END IF;
            RETURN NEW;
         ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE competition_groups_results SET group_results_ids = array_append(group_results_ids, NEW.id) WHERE id = NEW.group_results_id;
            UPDATE global_raiting SET score = (SELECT score FROM global_raiting WHERE couple_id = OLD.couple_id) - OLD.score WHERE couple_id = OLD.couple_id;
            UPDATE competition_groups_results SET group_results_ids = array_remove(group_results_ids, OLD.id) WHERE id = OLD.group_results_id;
            UPDATE global_raiting SET score = (SELECT score FROM global_raiting WHERE couple_id = NEW.couple_id) + NEW.score WHERE couple_id = NEW.couple_id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- возвращаемое значение для триггера AFTER не имеет значения
    END;
$global_comp_group_result_table$ LANGUAGE plpgsql;

CREATE TRIGGER upd_result_data_for_comp_results
AFTER INSERT OR UPDATE OR DELETE ON global_comp_group_result_table
    FOR EACH ROW EXECUTE PROCEDURE upd_result_data_for_comp_results();