Changes between Version 1 and Version 2 of Triggers


Ignore:
Timestamp:
09/25/25 21:19:16 (3 weeks ago)
Author:
225144
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Triggers

    v1 v2  
    11= Тригери
    2 == Проверка дали даден топик имат уникатно име во парент threadот
     2== Проверка дали даден топик има уникатно име во parent
    33{{{
    44CREATE OR REPLACE FUNCTION fn_validate_topic_title()
    … …  
    1010    IF new.title IN
    1111       (SELECT title
    12         FROM topic_thread
    13                  AS t
    14         WHERE t.parent_id = new.parent_id
    15            OR (t.parent_id IS NULL AND new.parent_id IS NULL)
     12        FROM topic_thread AS t
     13        WHERE t.referenced_by = new.referenced_by
     14           OR (t.referenced_by IS NULL AND new.referenced_by IS NULL)
    1615       )
    1716    THEN
    18         RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id;
     17        RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %', new.title, new.referenced_by;
    1918    END IF;
    2019    RETURN new;
    … …  
    2322}}}
    2423{{{
    25 CREATE OR REPLACE TRIGGER tr_check_topic_name --RADI
     24CREATE OR REPLACE TRIGGER tr_check_topic_name
    2625    BEFORE INSERT OR UPDATE
    2726    ON topic_thread
    … …  
    4039    v_user_id INT;
    4140BEGIN
    42     SELECT v_topic_thread.user_id
     41    SELECT v_topic_thread.is_created_by
    4342    INTO v_user_id
    4443    FROM v_topic_thread
    45     WHERE v_topic_thread.id = new.id;
    46     IF not check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN
    47         INSERT INTO moderator values (v_user_id);
    48     END IF;
    49     INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id);
    50     RETURN NEW;
    51 END
     44    WHERE v_topic_thread.id = NEW.id;
     45
     46    IF NOT check_if_user_exists_in('moderator', 'id', v_user_id::text) THEN
     47        INSERT INTO moderator VALUES (v_user_id);
     48    END IF;
     49
     50    INSERT INTO topic_thread_is_moderated_by_moderator(thread_id, user_id) VALUES (NEW.id, v_user_id);
     51    RETURN NEW;
     52END;
    5253$$;
    5354}}}
    … …  
    6364{{{
    6465CREATE OR REPLACE FUNCTION fn_remove_orphan_moderator()
    65     RETURNS trigger
    66     LANGUAGE plpgsql
    67 AS $function$
    68 BEGIN
    69     IF not exists (
    70         select 1
    71         from topic_threads_moderators t
    72         where t.user_id = OLD.user_id
    73     )
    74     THEN
    75         DELETE FROM moderator where id=OLD.user_id;
    76     END IF;
    77     IF not exists (
    78         select 1
    79         from topic_threads_moderators t
    80         where t.thread_id = OLD.thread_id
    81     )
    82     THEN
    83         delete from discussion_thread where parent_id=OLD.thread_id;
    84         DELETE FROM topic_thread where id = OLD.thread_id;
    85 --      delete from thread where id =  OLD.thread_id;
    86     END IF;
     66    RETURNS TRIGGER
     67    LANGUAGE plpgsql
     68AS
     69$$
     70BEGIN
     71    IF NOT EXISTS (
     72        SELECT 1
     73        FROM topic_thread_is_moderated_by_moderator t
     74        WHERE t.user_id = OLD.user_id
     75    ) THEN
     76        DELETE FROM moderator WHERE id = OLD.user_id;
     77    END IF;
     78
     79    IF NOT EXISTS (
     80        SELECT 1
     81        FROM topic_thread_is_moderated_by_moderator t
     82        WHERE t.thread_id = OLD.thread_id
     83    ) THEN
     84        DELETE FROM discussion_thread WHERE contained_in = OLD.thread_id;
     85        DELETE FROM topic_thread WHERE id = OLD.thread_id;
     86    END IF;
     87
    8788    RETURN OLD;
    8889END;
     90$$;
    8991}}}
    9092
    … …  
    9799}}}
    98100
    99 == При креирање на проект, креаторот се внесува како PROJECT_OWNER
     101== При креирање на проект, креаторот се внесува како PROJECT_MANAGER
    100102{{{
    101103CREATE OR REPLACE FUNCTION fn_insert_project_manager()
    … …  
    105107$$
    106108DECLARE
    107     usrId      INT;
     109    usrId INT;
    108110    new_project_id INT;
    109111BEGIN
    110     SELECT user_id, id
    111     into usrId,new_project_id
     112    SELECT is_created_by, id
     113    INTO usrId, new_project_id
    112114    FROM v_project_thread p
    113115    WHERE NEW.id = p.id;
    114     IF not EXISTS(
    115         select 1
    116         from developer_associated_with_project dawp
    117         where dawp.project_id=new_project_id and dawp.developer_id=usrId
    118     ) THEN
    119         INSERT INTO developer_associated_with_project(project_id, developer_id, started_at)
    120         values (new_project_id, usrId, NOW());
    121     end if;
    122     IF not check_if_user_exists_in('project_manager', 'id', usrId::text) THEN
     116
     117    IF NOT EXISTS(
     118        SELECT 1
     119        FROM developer_associated_with_project dawp
     120        WHERE dawp.in_project = new_project_id
     121          AND dawp.about_dev = usrId
     122    ) THEN
     123        INSERT INTO developer_associated_with_project(in_project, about_dev, started_at)
     124        VALUES (new_project_id, usrId, NOW());
     125    END IF;
     126
     127    IF NOT check_if_user_exists_in('project_manager', 'id', usrId::text) THEN
    123128        INSERT INTO project_manager VALUES (usrId);
    124     end if;
    125     RETURN NEW;
    126 END
     129    END IF;
     130
     131    RETURN NEW;
     132END;
    127133$$;
    128134}}}
    … …  
    140146
    141147{{{
    142 create or replace function fn_delete_dangling_tags()
    143     RETURNS trigger
    144     LANGUAGE plpgsql
    145 AS $$
    146 BEGIN
    147     IF NOT EXISTS(select 1
    148                   from tag_threads
    149                   where tag_name = OLD.tag_name
    150                   group by tag_name) THEN
    151         delete from tag where name = OLD.tag_name;
    152     end if;
     148CREATE OR REPLACE FUNCTION fn_delete_dangling_tags()
     149    RETURNS TRIGGER
     150    LANGUAGE plpgsql
     151AS
     152$$
     153BEGIN
     154    IF NOT EXISTS (
     155        SELECT 1
     156        FROM tag_assigned_to_thread
     157        WHERE tag_name = OLD.tag_name
     158        GROUP BY tag_name
     159    ) THEN
     160        DELETE FROM tag WHERE name = OLD.tag_name;
     161    END IF;
    153162    RETURN OLD;
    154163END;
    … …  
    166175== Внесување на даден корисник како developer, откако ќе стани дел од даден проект
    167176{{{
    168 create or replace function fn_add_dev_if_not_exist()
    169     returns trigger
    170     language plpgsql
    171 as $$
    172 BEGIN
    173     IF NOT check_if_user_exists_in('developer','id',new.developer_id::text) THEN
    174         INSERT INTO developer values (NEW.developer_id);
    175     end if;
    176     RETURN new;
    177 end;
     177CREATE OR REPLACE FUNCTION fn_add_dev_if_not_exist()
     178    RETURNS TRIGGER
     179    LANGUAGE plpgsql
     180AS
     181$$
     182BEGIN
     183    IF NOT check_if_user_exists_in('developer', 'id', NEW.about_dev::text) THEN
     184        INSERT INTO developer VALUES (NEW.about_dev);
     185    END IF;
     186    RETURN NEW;
     187END;
    178188$$;
    179189
    … …  
    189199== Креирање на General канал, при креирање на даден проект
    190200{{{
    191 create or replace function fn_insert_general_for_project()
    192     returns trigger
    193     language plpgsql
    194 as $$
     201CREATE OR REPLACE FUNCTION fn_insert_general_for_project()
     202    RETURNS TRIGGER
     203    LANGUAGE plpgsql
     204AS
     205$$
    195206DECLARE
    196207    developer_id INT;
    197     project_resource_id INT;
    198 BEGIN
    199     select user_id
    200     into developer_id
    201     from thread t
    202     where t.id=NEW.id;
    203     insert into project_resource default values returning id into project_resource_id;
    204 
    205     insert into channel(name,description,project_id,developer_id,project_resource_id)
    206     values ('General','General',NEW.id,developer_id,project_resource_id);
    207 
    208     return new;
    209 end;
     208BEGIN
     209    SELECT is_created_by
     210    INTO developer_id
     211    FROM thread t
     212    WHERE t.id = NEW.id;
     213
     214    INSERT INTO channel(name, description, project_has, constructed_by)
     215    VALUES ('General', 'General', NEW.id, developer_id);
     216
     217    RETURN NEW;
     218END;
    210219$$;
    211220}}}
    … …  
    223232    RETURNS TRIGGER
    224233    LANGUAGE plpgsql
    225 AS $$
    226 BEGIN
    227     RAISE NOTICE 'user_id: %, topic_id: %', NEW.user_id, NEW.topic_id;
    228 
    229 UPDATE submission
    230 SET status = 'ACCEPTED'
    231 WHERE id in (
    232     select id
    233     from report r
    234     where r.for_user_id = NEW.user_id and r.thread_id = NEW.topic_id
    235 );
    236 
    237 RETURN NEW;
     234AS
     235$$
     236BEGIN
     237    RAISE NOTICE 'user_id: %, topic_id: %', NEW.refers_to, NEW.blacklisted_from;
     238
     239    UPDATE submission
     240    SET status = 'ACCEPTED'
     241    WHERE id IN (
     242        SELECT id
     243        FROM report r
     244        WHERE r.about = NEW.refers_to
     245          AND r.for_misconduct_in = NEW.blacklisted_from
     246    );
     247
     248    RETURN NEW;
    238249END;
    239250$$;
    … …  
    251262
    252263{{{
    253 create or replace function fn_add_blacklisted_user()
    254 RETURNS trigger
    255 LANGUAGE plpgsql
    256 AS $$
    257 BEGIN
    258         IF NOT EXISTS(
    259                 select 1
    260                 from blacklisted_user
    261                 where  topic_id = NEW.topic_id and user_id = NEW.user_id and end_date is NULL
    262         )
    263         THEN
    264                 RETURN NEW;
    265 END IF;
    266 RETURN NULL;
     264CREATE OR REPLACE FUNCTION fn_add_blacklisted_user()
     265    RETURNS TRIGGER
     266    LANGUAGE plpgsql
     267AS
     268$$
     269BEGIN
     270    IF NOT EXISTS (
     271        SELECT 1
     272        FROM blacklisted_user
     273        WHERE blacklisted_from = NEW.blacklisted_from
     274          AND refers_to = NEW.refers_to
     275          AND end_date IS NULL
     276    ) THEN
     277        RETURN NEW;
     278    END IF;
     279    RETURN NULL;
    267280END;
    268281$$;
    … …  
    275288execute function fn_add_blacklisted_user();
    276289}}}
    277 
    278 == Автоматско додавање на project_resource, при додавање на канал
    279 {{{
    280 create or replace function fn_add_project_resource()
    281     returns trigger
    282     language plpgsql
    283 as $$
    284 DECLARE
    285     project_resource_id INT;
    286 BEGIN
    287     insert into project_resource default values returning id into project_resource_id;
    288     new.project_resource_id := project_resource_id;
    289     return new;
    290 end;
    291 $$;
    292 }}}
    293 
    294 {{{
    295 create or replace trigger tr_add_project_resource_channel
    296     before insert
    297     on channel
    298     for each row
    299 execute function fn_add_project_resource();
    300 }}}