Тригери

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

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.parent_id = new.parent_id
           OR (t.parent_id IS NULL AND new.parent_id IS NULL)
       )
    THEN
        RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id;
    END IF;
    RETURN new;
END;
$$;

CREATE OR REPLACE TRIGGER tr_check_topic_name --RADI
    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.user_id
    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_threads_moderators(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 $function$
BEGIN
    IF not exists (
        select 1
        from topic_threads_moderators 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_threads_moderators t
        where t.thread_id = OLD.thread_id
    )
    THEN
        delete from discussion_thread where parent_id=OLD.thread_id;
        DELETE FROM topic_thread where id = OLD.thread_id;
-- 	delete from 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_OWNER

CREATE OR REPLACE FUNCTION fn_insert_project_manager()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
DECLARE
    usrId      INT;
    new_project_id INT;
BEGIN
    SELECT user_id, 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.project_id=new_project_id and dawp.developer_id=usrId
    ) THEN
        INSERT INTO developer_associated_with_project(project_id, developer_id, 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_threads
                  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.developer_id::text) THEN
        INSERT INTO developer values (NEW.developer_id);
    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;
    project_resource_id INT;
BEGIN
    select user_id
    into developer_id
    from thread t
    where t.id=NEW.id;
    insert into project_resource default values returning id into project_resource_id;

    insert into channel(name,description,project_id,developer_id,project_resource_id)
    values ('General','General',NEW.id,developer_id,project_resource_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.user_id, NEW.topic_id;

UPDATE submission
SET status = 'ACCEPTED'
WHERE id in (
    select id
    from report r
    where r.for_user_id = NEW.user_id and r.thread_id = NEW.topic_id
);

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  topic_id = NEW.topic_id and user_id = NEW.user_id 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();

Автоматско додавање на project_resource, при додавање на канал

create or replace function fn_add_project_resource()
    returns trigger
    language plpgsql
as $$
DECLARE
    project_resource_id INT;
BEGIN
    insert into project_resource default values returning id into project_resource_id;
    new.project_resource_id := project_resource_id;
    return new;
end;
$$;

create or replace trigger tr_add_project_resource_channel
    before insert
    on channel
    for each row
execute function fn_add_project_resource();