Тригери

Проверка дали даден топик има уникатно име во parent

CREATE OR REPLACE FUNCTION fn_validate_topic_title()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF new.title IN
       (SELECT title
        FROM topic_thread AS t
        WHERE t.referenced_by = new.referenced_by
           OR (t.referenced_by IS NULL AND new.referenced_by IS NULL)
       )
    THEN
        RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %', new.title, new.referenced_by;
    END IF;
    RETURN new;
END;
$$;

CREATE OR REPLACE TRIGGER tr_check_topic_name
    BEFORE INSERT OR UPDATE
    ON topic_thread
    FOR EACH ROW
EXECUTE FUNCTION fn_validate_topic_title();

При креирање на топик, креаторот се внесува како модератор

CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_user_id INT;
BEGIN
    SELECT v_topic_thread.is_created_by
    INTO v_user_id
    FROM v_topic_thread
    WHERE v_topic_thread.id = NEW.id;

    IF NOT check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN
        INSERT INTO moderator VALUES (v_user_id);
    END IF;

    INSERT INTO topic_thread_is_moderated_by_moderator(thread_id, user_id) VALUES (NEW.id, v_user_id);
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator 
    AFTER INSERT
    ON topic_thread
    FOR EACH ROW
EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();

При бришење на topic, проверка дали модераторот сеуште модерира topicци

CREATE OR REPLACE FUNCTION fn_remove_orphan_moderator()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM topic_thread_is_moderated_by_moderator t
        WHERE t.user_id = OLD.user_id
    ) THEN
        DELETE FROM moderator WHERE id = OLD.user_id;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM topic_thread_is_moderated_by_moderator t
        WHERE t.thread_id = OLD.thread_id
    ) THEN
        DELETE FROM discussion_thread WHERE contained_in = OLD.thread_id;
        DELETE FROM topic_thread WHERE id = OLD.thread_id;
    END IF;

    RETURN OLD;
END;
$$;

CREATE OR REPLACE TRIGGER tr_remove_orphan_moderator 
    AFTER DELETE
    ON topic_threads_moderators
    FOR EACH ROW
EXECUTE FUNCTION fn_remove_orphan_moderator();

При креирање на проект, креаторот се внесува како PROJECT_MANAGER

CREATE OR REPLACE FUNCTION fn_insert_project_manager()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    usrId INT;
    new_project_id INT;
BEGIN
    SELECT is_created_by, id
    INTO usrId, new_project_id
    FROM v_project_thread p
    WHERE NEW.id = p.id;

    IF NOT EXISTS(
        SELECT 1
        FROM developer_associated_with_project dawp
        WHERE dawp.in_project = new_project_id
          AND dawp.about_dev = usrId
    ) THEN
        INSERT INTO developer_associated_with_project(in_project, about_dev, started_at)
        VALUES (new_project_id, usrId, NOW());
    END IF;

    IF NOT check_if_user_exists_in('project_manager', 'id', usrId::text) THEN
        INSERT INTO project_manager VALUES (usrId);
    END IF;

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER tr_a_insert_project_manager --RADI
    AFTER INSERT
    ON project_thread
    FOR EACH ROW
EXECUTE FUNCTION fn_insert_project_manager();

Бришење на не користени тагови

CREATE OR REPLACE FUNCTION fn_delete_dangling_tags()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM tag_assigned_to_thread
        WHERE tag_name = OLD.tag_name
        GROUP BY tag_name
    ) THEN
        DELETE FROM tag WHERE name = OLD.tag_name;
    END IF;
    RETURN OLD;
END;
$$;

create or replace trigger tr_delete_dangling_tags
    after delete
    on tag_threads
    for each row
    execute function fn_delete_dangling_tags();

Внесување на даден корисник како developer, откако ќе стани дел од даден проект

CREATE OR REPLACE FUNCTION fn_add_dev_if_not_exist()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT check_if_user_exists_in('developer', 'id', NEW.about_dev::text) THEN
        INSERT INTO developer VALUES (NEW.about_dev);
    END IF;
    RETURN NEW;
END;
$$;

create or replace trigger tr_add_dev_if_not_exist 
    before insert on developer_associated_with_project
    for each row
execute function fn_add_dev_if_not_exist();

Креирање на General канал, при креирање на даден проект

CREATE OR REPLACE FUNCTION fn_insert_general_for_project()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    developer_id INT;
BEGIN
    SELECT is_created_by
    INTO developer_id
    FROM thread t
    WHERE t.id = NEW.id;

    INSERT INTO channel(name, description, project_has, constructed_by)
    VALUES ('General', 'General', NEW.id, developer_id);

    RETURN NEW;
END;
$$;

create or replace trigger tr_insert_general_for_project 
    after insert on project_thread
    for each row
execute function fn_insert_general_for_project();

Сите 'pending' пријави за даден корисник стануваат 'accepted', при додавање на тој корисник во 'blacklist' листата.

CREATE OR REPLACE FUNCTION fn_change_status_on_pending_reports()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    RAISE NOTICE 'user_id: %, topic_id: %', NEW.refers_to, NEW.blacklisted_from;

    UPDATE submission
    SET status = 'ACCEPTED'
    WHERE id IN (
        SELECT id
        FROM report r
        WHERE r.about = NEW.refers_to
          AND r.for_misconduct_in = NEW.blacklisted_from
    );

    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER tr_change_status_on_pending_reports
    AFTER INSERT
    ON blacklisted_user
    FOR EACH ROW
EXECUTE FUNCTION fn_change_status_on_pending_reports();

Оневозможувено повторно додавање на корисник во 'blacklist' листата, доколку е веќе присутен на неа

CREATE OR REPLACE FUNCTION fn_add_blacklisted_user()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM blacklisted_user
        WHERE blacklisted_from = NEW.blacklisted_from
          AND refers_to = NEW.refers_to
          AND end_date IS NULL
    ) THEN
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$;

create or replace trigger tr_add_blacklisted_user
before insert on blacklisted_user
for each row
execute function fn_add_blacklisted_user();