Changes between Version 1 and Version 2 of DatabaseProgramming


Ignore:
Timestamp:
09/16/26 06:57:59 (11 days ago)
Author:
235013
Comment:

Фаза 4: документација

Legend:

Unmodified
Added
Removed
Modified

  • DatabaseProgramming

    v1 v2  
    1 = DatabaseProgramming =
    2 
    3 == Опис ==
    4 
    5 Покрај основната структура на базата, имплементирана е и дополнителна PostgreSQL логика со функции, процедури и тригери.
    6 
    7 Оваа логика се користи за:
    8 
    9 * проверка на валидноста на податоците
    10 * извршување на почести операции над повеќе табели
    11 * автоматско одржување на историја и timestamps
    12 * зачувување на интегритетот на податоците
    13 * спроведување на дел од бизнис правилата на системот
    14 
    15 Кодот е поделен во:
    16 
    17 * [attachment:functions.sql functions.sql]
    18 * [attachment:procedures.sql procedures.sql]
    19 * [attachment:triggers.sql triggers.sql]
     1= Функции, процедури и тригери (!DatabaseProgramming) =
     2
     3Апликациската логика е имплементирана во PL/pgSQL: 11 процедури ([attachment:procedures.sql]), 18 функции ([attachment:functions.sql]) и 18 тригери со 10 тригер-функции ([attachment:triggers.sql]). Правилата се поделени во три слоја. Ограничувањата од шемата ги гарантираат правилата за секоја редица. Процедурите ги проверуваат истите правила пред запишувањето, за апликацијата да добие описна порака наместо грешка на ограничување; тие проверки се издвоени во функции. Правилата што читаат други табели ги гарантираат тригерите за секој пат на запишување; процедурите не ги повторуваат, туку се потпираат на тригерот.
     4
     5[[PageOutline(2-3,Содржина,inline)]]
     6
     7----
     8
     9== Процедури ==
     10
     11=== Процедура 1: Регистрација на корисник (sp_register_user) ===
     12
     13Регистрација на сметка: во една трансакција запишува нов корисник во `"User"` и соодветната редица во табелата на неговиот подтип (`Client_User`, `Vendor_User` или `Management_User`). Типот определува кој идентификатор е задолжителен (клиент, софтверска агенција или улога) и тој мора да постои; текстуалните полиња не смеат да бидат празни. Корисникот се креира неактивен и се активира по потврда.
     14
     15{{{#!sql
     16CREATE OR REPLACE PROCEDURE sp_register_user(
     17    OUT p_user_id       int4,
     18    IN  p_type          text,
     19    IN  p_first_name    text,
     20    IN  p_last_name     text,
     21    IN  p_email         text,
     22    IN  p_password_hash text,
     23    IN  p_client_id     int4 DEFAULT NULL,
     24    IN  p_vendor_id     int4 DEFAULT NULL,
     25    IN  p_role_id       int4 DEFAULT NULL
     26)
     27LANGUAGE plpgsql AS $$
     28BEGIN
     29    -- Типот одредува кој идентификатор е задолжителен; секоја гранка го проверува својот
     30    CASE p_type
     31        WHEN 'client' THEN
     32            IF p_client_id IS NULL THEN
     33                RAISE EXCEPTION 'p_client_id is required when registering a client user';
     34            END IF;
     35            PERFORM fn_assert_client_exists(p_client_id);
     36        WHEN 'vendor' THEN
     37            IF p_vendor_id IS NULL THEN
     38                RAISE EXCEPTION 'p_vendor_id is required when registering a vendor user';
     39            END IF;
     40            PERFORM fn_assert_vendor_exists(p_vendor_id);
     41        WHEN 'management' THEN
     42            IF p_role_id IS NULL THEN
     43                RAISE EXCEPTION 'p_role_id is required when registering a management user';
     44            END IF;
     45            PERFORM fn_assert_role_exists(p_role_id);
     46        ELSE
     47            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
     48    END CASE;
     49
     50    -- Задолжителните текстуални полиња не смеат да бидат празни
     51    PERFORM fn_assert_not_blank(p_first_name, 'first_name');
     52    PERFORM fn_assert_not_blank(p_last_name, 'last_name');
     53    PERFORM fn_assert_not_blank(p_email, 'email');
     54    PERFORM fn_assert_not_blank(p_password_hash, 'password_hash');
     55
     56    -- Корисникот се креира неактивен (is_active DEFAULT false);
     57    -- sp_activate_user е чекорот на потврда што го активира
     58    INSERT INTO "User" (type, first_name, last_name, email, password_hash)
     59    VALUES (p_type, p_first_name, p_last_name, p_email, p_password_hash)
     60    RETURNING user_id INTO p_user_id;  -- RETURNING го дава новиот клуч во OUT-параметарот
     61
     62    -- Редицата на подтипот; тригерот trg_enforce_user_subtype ја проверува според "User".type
     63    CASE p_type
     64        WHEN 'client' THEN
     65            INSERT INTO Client_User (user_id, client_id)
     66            VALUES (p_user_id, p_client_id);
     67        WHEN 'vendor' THEN
     68            INSERT INTO Vendor_User (user_id, vendor_id)
     69            VALUES (p_user_id, p_vendor_id);
     70        WHEN 'management' THEN
     71            INSERT INTO Management_User (user_id, role_id)
     72            VALUES (p_user_id, p_role_id);
     73        ELSE
     74            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
     75    END CASE;
     76END;
     77$$;
     78
     79CALL sp_register_user(NULL, 'client', 'Ана', 'Петрова', 'ana.petrova@example.com', 'sha256:...', p_client_id => 17);
     80}}}
     81
     82----
     83
     84=== Процедура 2: Активирање на корисник (sp_activate_user) ===
     85
     86Потврда на регистрираната сметка: го активира корисникот. Непостоечки или веќе активен корисник се одбива. Заедно со регистрацијата и деактивирањето го затвора животниот циклус на сметката.
     87
     88{{{#!sql
     89CREATE OR REPLACE PROCEDURE sp_activate_user(
     90    IN p_user_id int4
     91)
     92LANGUAGE plpgsql AS $$
     93BEGIN
     94    -- Состојбата е дел од условот WHERE:
     95    -- една наредба истовремено проверува и запишува
     96    UPDATE "User"
     97    SET    is_active = true
     98    WHERE  user_id   = p_user_id
     99      AND  is_active = false;
     100
     101    IF NOT FOUND THEN
     102        RAISE EXCEPTION 'User % does not exist or is already active', p_user_id;
     103    END IF;
     104END;
     105$$;
     106
     107CALL sp_activate_user(77001);
     108}}}
     109
     110----
     111
     112=== Процедура 3: Деактивирање на корисник (sp_deactivate_user) ===
     113
     114Ја деактивира сметката наместо да ја брише, бидејќи историјата (рецензии, спорови, промени на статус) упатува на корисникот што дејствувал. Ако корисникот е менаџмент корисник, отворените спорови што му се доделени се ослободуваат, за ниту еден спор да не остане кај корисник што заминал.
     115
     116{{{#!sql
     117CREATE OR REPLACE PROCEDURE sp_deactivate_user(
     118    IN p_user_id int4
     119)
     120LANGUAGE plpgsql AS $$
     121DECLARE
     122    v_is_management bool;
     123BEGIN
     124    -- Состојбата е дел од условот WHERE, како кај sp_activate_user
     125    UPDATE "User"
     126    SET    is_active = false
     127    WHERE  user_id   = p_user_id
     128      AND  is_active = true;
     129
     130    IF NOT FOUND THEN
     131        RAISE EXCEPTION 'User % does not exist or is already inactive', p_user_id;
     132    END IF;
     133
     134    -- Менаџмент корисник што заминува не смее да остане доделен на отворени спорови
     135    SELECT EXISTS (
     136        SELECT 1 FROM Management_User WHERE user_id = p_user_id
     137    ) INTO v_is_management;
     138
     139    IF v_is_management THEN
     140        UPDATE Dispute_Ticket
     141        SET    assigned_management_user_id = NULL
     142        WHERE  assigned_management_user_id = p_user_id
     143          AND  is_resolved                 = false;
     144    END IF;
     145END;
     146$$;
     147
     148CALL sp_deactivate_user(77001);
     149}}}
     150
     151----
     152
     153=== Процедура 4: Договор со прв проект (sp_create_contract_with_project) ===
     154
     155Склучување договор меѓу клиент и софтверска агенција заедно со првиот проект на договорот, во една трансакција, бидејќи во моделот проектот секогаш припаѓа на договор. Пред запишувањето проверува дека насловот, името на проектот и буџетот се валидни и дека клиентот, софтверската агенција и почетниот статус постојат; ги враќа двата нови клуча.
     156
     157{{{#!sql
     158CREATE OR REPLACE PROCEDURE sp_create_contract_with_project(
     159    OUT p_contract_id        int4,
     160    OUT p_project_id         int4,
     161    IN  p_client_id          int4,
     162    IN  p_vendor_id          int4,
     163    IN  p_contract_title     text,
     164    IN  p_project_name       text,
     165    IN  p_status_id          int4,
     166    IN  p_budget             numeric(10,2),
     167    IN  p_contract_number    text          DEFAULT NULL,
     168    IN  p_cvc_start_date     date          DEFAULT CURRENT_DATE,
     169    IN  p_cvc_end_date       date          DEFAULT NULL,
     170    IN  p_total_value        numeric(10,2) DEFAULT NULL,
     171    IN  p_currency_code      text          DEFAULT NULL,
     172    IN  p_terms_summary      text          DEFAULT NULL,
     173    IN  p_project_start_date date          DEFAULT CURRENT_DATE,
     174    IN  p_project_end_date   date          DEFAULT NULL
     175)
     176LANGUAGE plpgsql AS $$
     177BEGIN
     178    -- Задолжителните текстуални полиња не смеат да бидат празни
     179    PERFORM fn_assert_not_blank(p_contract_title, 'contract_title');
     180    PERFORM fn_assert_not_blank(p_project_name, 'project_name');
     181
     182    -- Буџетот мора да биде даден и позитивен
     183    PERFORM fn_assert_positive(p_budget, 'Budget');
     184
     185    -- Референцираните редици мора да постојат пред запишувањето
     186    PERFORM fn_assert_client_exists(p_client_id);
     187    PERFORM fn_assert_vendor_exists(p_vendor_id);
     188    PERFORM fn_assert_status_exists(p_status_id);
     189
     190    INSERT INTO Client_Vendor_Contract (
     191        client_id, vendor_id, contract_title, contract_number,
     192        start_date, end_date, total_value, currency_code, terms_summary
     193    )
     194    VALUES (
     195        p_client_id, p_vendor_id, p_contract_title, p_contract_number,
     196        p_cvc_start_date, p_cvc_end_date, p_total_value, p_currency_code, p_terms_summary
     197    )
     198    RETURNING contract_id INTO p_contract_id;
     199
     200    -- Името на проектот е уникатно внатре во договорот (uq_project_contract_name);
     201    -- договорот штотуку е создаден, па првиот проект не може да се судри со друг
     202    INSERT INTO Project (
     203        contract_id, status_id, project_name, start_date, end_date, budget
     204    )
     205    VALUES (
     206        p_contract_id, p_status_id, p_project_name,
     207        p_project_start_date, p_project_end_date, p_budget
     208    )
     209    RETURNING project_id INTO p_project_id;
     210END;
     211$$;
     212
     213CALL sp_create_contract_with_project(NULL, NULL, 17, 42, 'Развој на веб-портал', 'Веб-портал, фаза 1', 1, 125000.00,
     214                                     p_total_value => 500000.00, p_currency_code => 'EUR');
     215}}}
     216
     217----
     218
     219=== Процедура 5: Промена на статус на проект (sp_update_project_status) ===
     220
     221Го води животниот циклус на проектот: го менува статусот и запишува редица во историјата на статуси со точно еден актер, корисник на софтверската агенција или менаџмент корисник. Новиот статус мора да постои; дека проектот и актерот постојат и дека корисникот на агенцијата ѝ припаѓа на агенцијата на проектот го проверува тригерот при запишувањето во историјата. Ако проектот веќе е во целниот статус, не се запишува ништо. Проект што веќе има рецензија не може да се врати во отворен статус.
     222
     223{{{#!sql
     224CREATE OR REPLACE PROCEDURE sp_update_project_status(
     225    IN p_project_id         int4,
     226    IN p_new_status_id      int4,
     227    IN p_vendor_user_id     int4 DEFAULT NULL,
     228    IN p_management_user_id int4 DEFAULT NULL,
     229    IN p_comment            text DEFAULT NULL
     230)
     231LANGUAGE plpgsql AS $$
     232DECLARE
     233    v_current_status_id int4;
     234BEGIN
     235    -- Точно еден актер: корисник на агенцијата или менаџмент корисник, никогаш двата или ниту еден
     236    IF (p_vendor_user_id IS NULL) = (p_management_user_id IS NULL) THEN
     237        RAISE EXCEPTION
     238            'Provide exactly one actor: p_vendor_user_id or p_management_user_id';
     239    END IF;
     240
     241    PERFORM fn_assert_status_exists(p_new_status_id);
     242
     243    -- Дека проектот и актерот постојат и дека актерот од агенцијата ѝ припаѓа
     244    -- на агенцијата на проектот го проверува тригерот trg_status_history_actor
     245    -- при INSERT во Project_Status_History подолу
     246
     247    -- Ако проектот веќе е во целниот статус, не се запишува ништо
     248    SELECT status_id INTO v_current_status_id
     249    FROM Project
     250    WHERE project_id = p_project_id;
     251
     252    IF v_current_status_id = p_new_status_id THEN
     253        RAISE NOTICE 'Project % is already at status % – no update performed',
     254            p_project_id, p_new_status_id;
     255        RETURN;
     256    END IF;
     257
     258    -- Рецензиран проект останува завршен: не може да се врати во отворен статус
     259    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id)
     260       AND (SELECT status_name FROM Project_Status WHERE status_id = p_new_status_id)
     261           NOT IN ('Completed', 'Cancelled') THEN
     262        RAISE EXCEPTION 'Project % has a review and cannot return to an open status', p_project_id;
     263    END IF;
     264
     265    UPDATE Project
     266    SET    status_id = p_new_status_id
     267    WHERE  project_id = p_project_id;
     268
     269    INSERT INTO Project_Status_History (
     270        project_id, status_id, vendor_user_id, management_user_id, comment
     271    )
     272    VALUES (
     273        p_project_id, p_new_status_id, p_vendor_user_id, p_management_user_id, p_comment
     274    );
     275END;
     276$$;
     277
     278CALL sp_update_project_status(1400001, 3, NULL, 75001, 'Одобрено од менаџмент');
     279}}}
     280
     281----
     282
     283=== Процедура 6: Промена на буџет на проект (sp_update_project_budget) ===
     284
     285Го менува буџетот на проектот; новиот буџет мора да биде позитивен, а проектот да постои. Ревизиската редица со стариот и новиот буџет ја запишува тригерот за ревизија на буџетот, така што и промена надвор од процедурата останува забележана.
     286
     287{{{#!sql
     288CREATE OR REPLACE PROCEDURE sp_update_project_budget(
     289    IN p_project_id int4,
     290    IN p_new_budget numeric(10,2)
     291)
     292LANGUAGE plpgsql AS $$
     293BEGIN
     294    PERFORM fn_assert_positive(p_new_budget, 'Budget');
     295
     296    -- Ревизиската редица ја запишува trg_capture_budget_change, преку тригерот
     297    -- trg_project_budget_audit и неговата клаузула WHEN (само кога буџетот се менува)
     298    UPDATE Project
     299    SET    budget = p_new_budget
     300    WHERE  project_id = p_project_id;
     301
     302    -- Ниту една погодена редица значи дека проектот не постои; проверката ја дава пораката
     303    IF NOT FOUND THEN
     304        PERFORM fn_assert_project_exists(p_project_id);
     305    END IF;
     306END;
     307$$;
     308
     309CALL sp_update_project_budget(1400001, 175000.00);
     310}}}
     311
     312----
     313
     314=== Процедура 7: Поднесување рецензија (sp_submit_review) ===
     315
     316Поднесување рецензија од клиент по завршен проект: една рецензија и нејзините оценки по димензии, во една трансакција; оценките се предаваат како JSON низа со барем една димензија (не мора да бидат оценети сите десет). Авторот мора да биде корисник на клиентот од договорот на проектот, проектот да биде завршен (Completed или Cancelled, што го гарантира тригерот 18) и да нема веќе рецензија, димензиите да постојат без повторување и оценките да се меѓу 1 и 5. Рецензијата се создава необјавена.
     317
     318{{{#!sql
     319CREATE OR REPLACE PROCEDURE sp_submit_review(
     320    OUT p_review_id      int4,
     321    IN  p_project_id     int4,
     322    IN  p_client_user_id int4,
     323    IN  p_summary_text   text,
     324    IN  p_scores         jsonb
     325)
     326LANGUAGE plpgsql AS $$
     327DECLARE
     328    v_dimension_id int4;
     329    v_score_value  int4;
     330BEGIN
     331    -- Резимето не смее да биде празно
     332    PERFORM fn_assert_not_blank(p_summary_text, 'summary_text');
     333
     334    -- Обликот на p_scores: непразна JSON низа од објекти, секој со
     335    -- нумерички dimension_id и score_value
     336    -- jsonb_typeof го враќа JSON типот на вредноста ('array', 'object', 'number', ...)
     337    IF p_scores IS NULL OR jsonb_typeof(p_scores) <> 'array' THEN
     338        RAISE EXCEPTION 'p_scores must be a JSON array of {dimension_id, score_value} objects';
     339    END IF;
     340
     341    IF jsonb_array_length(p_scores) = 0 THEN
     342        RAISE EXCEPTION 'p_scores must contain at least one dimension score';
     343    END IF;
     344
     345    -- jsonb_array_elements ја разложува низата во по една редица за секој елемент (v)
     346    IF EXISTS (
     347        SELECT 1
     348        FROM jsonb_array_elements(p_scores) AS t(v)
     349        WHERE jsonb_typeof(v) <> 'object'
     350           OR jsonb_typeof(v->'dimension_id') IS DISTINCT FROM 'number'
     351           OR jsonb_typeof(v->'score_value') IS DISTINCT FROM 'number'
     352    ) THEN
     353        RAISE EXCEPTION
     354            'Each element of p_scores must be an object with numeric dimension_id and score_value';
     355    END IF;
     356
     357    -- Проектот и клиентскиот корисник мора да постојат
     358    PERFORM fn_assert_project_exists(p_project_id);
     359    PERFORM fn_assert_client_user_exists(p_client_user_id);
     360
     361    -- Корисникот мора да припаѓа на клиентот од договорот на проектот;
     362    -- NULL (непознат корисник или проект) се смета за несовпаѓање
     363    IF NOT coalesce(fn_is_client_user_of_project(p_client_user_id, p_project_id), false) THEN
     364        RAISE EXCEPTION
     365            'Client user % does not belong to the client associated with project %',
     366            p_client_user_id, p_project_id;
     367    END IF;
     368
     369    -- Дека проектот е завршен (Completed или Cancelled) го проверува тригерот
     370    -- trg_review_finished_project при INSERT во Review подолу
     371
     372    -- Најмногу една рецензија по проект (UNIQUE врз Review.project_id)
     373    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id) THEN
     374        RAISE EXCEPTION 'A review already exists for project %', p_project_id;
     375    END IF;
     376
     377    -- Ниту една димензија не смее да се повторува во низата
     378    IF EXISTS (
     379        SELECT 1
     380        FROM (
     381            SELECT (v->>'dimension_id')::int4 AS dim_id
     382            FROM jsonb_array_elements(p_scores) AS t(v)
     383        ) dims
     384        GROUP BY dim_id
     385        HAVING COUNT(*) > 1
     386    ) THEN
     387        RAISE EXCEPTION 'Duplicate dimension_id found in p_scores';
     388    END IF;
     389
     390    -- Секоја димензија мора да постои: се бара првата што не постои
     391    -- и проверката fn_assert_dimension_exists ја дава пораката за неа
     392    SELECT (v->>'dimension_id')::int4
     393    INTO   v_dimension_id
     394    FROM   jsonb_array_elements(p_scores) AS t(v)
     395    WHERE  NOT EXISTS (
     396        SELECT 1 FROM Rating_Dimension rd
     397        WHERE rd.dimension_id = (v->>'dimension_id')::int4
     398    )
     399    LIMIT  1;
     400
     401    IF FOUND THEN
     402        PERFORM fn_assert_dimension_exists(v_dimension_id);
     403    END IF;
     404
     405    -- Секоја оценка мора да биде меѓу 1 и 5; ->> го чита полето како текст, ::int4 го претвора во број
     406    SELECT (v->>'score_value')::int4
     407    INTO   v_score_value
     408    FROM   jsonb_array_elements(p_scores) AS t(v)
     409    WHERE  (v->>'score_value')::int4 NOT BETWEEN 1 AND 5
     410    LIMIT  1;
     411
     412    IF FOUND THEN
     413        RAISE EXCEPTION 'Score value must be between 1 and 5 (received: %)', v_score_value;
     414    END IF;
     415
     416    INSERT INTO Review (project_id, client_user_id, summary_text)
     417    VALUES (p_project_id, p_client_user_id, p_summary_text)
     418    RETURNING review_id INTO p_review_id;
     419
     420    -- По една редица за секој елемент од низата, со еден INSERT ... SELECT
     421    INSERT INTO Review_Score (review_id, dimension_id, score_value)
     422    SELECT p_review_id, (v->>'dimension_id')::int4, (v->>'score_value')::int4
     423    FROM   jsonb_array_elements(p_scores) AS t(v);
     424END;
     425$$;
     426
     427CALL sp_submit_review(NULL, 1400001, 5001, 'Solid delivery, good communication.',
     428                      '[{"dimension_id": 1, "score_value": 5}, {"dimension_id": 2, "score_value": 4}]');
     429}}}
     430
     431----
     432
     433=== Процедура 8: Поднесување спор (sp_file_dispute) ===
     434
     435Оспорување на рецензија од страна на софтверската агенција: нејзин корисник отвора спор врз рецензијата. Корисникот мора да припаѓа на агенцијата што го испорачала рецензираниот проект и не смее веќе да има отворен спор за истата рецензија. Додека спорот е отворен, рецензијата не може да се објави.
     436
     437{{{#!sql
     438CREATE OR REPLACE PROCEDURE sp_file_dispute(
     439    OUT p_ticket_id      int4,
     440    IN  p_review_id      int4,
     441    IN  p_vendor_user_id int4,
     442    IN  p_reason         text
     443)
     444LANGUAGE plpgsql AS $$
     445DECLARE
     446    v_project_id int4;
     447BEGIN
     448    -- Причината не смее да биде празна
     449    PERFORM fn_assert_not_blank(p_reason, 'reason');
     450
     451    PERFORM fn_assert_review_exists(p_review_id);
     452
     453    -- Корисникот на агенцијата мора да постои
     454    PERFORM fn_assert_vendor_user_exists(p_vendor_user_id);
     455
     456    -- Истиот корисник не смее да има два отворени спора за иста рецензија
     457    IF EXISTS (
     458        SELECT 1 FROM Dispute_Ticket
     459        WHERE review_id      = p_review_id
     460          AND vendor_user_id = p_vendor_user_id
     461          AND is_resolved    = false
     462    ) THEN
     463        RAISE EXCEPTION
     464            'Vendor user % already has an unresolved dispute on review %',
     465            p_vendor_user_id, p_review_id;
     466    END IF;
     467
     468    -- Корисникот мора да припаѓа на агенцијата што го испорачала рецензираниот
     469    -- проект; NULL (непознат корисник или проект) се смета за несовпаѓање
     470    SELECT project_id
     471    INTO   v_project_id
     472    FROM   Review
     473    WHERE  review_id = p_review_id;
     474
     475    IF NOT coalesce(fn_is_vendor_user_of_project(p_vendor_user_id, v_project_id), false) THEN
     476        RAISE EXCEPTION
     477            'Vendor user % does not belong to the vendor associated with review %',
     478            p_vendor_user_id, p_review_id;
     479    END IF;
     480
     481    INSERT INTO Dispute_Ticket (review_id, vendor_user_id, reason)
     482    VALUES (p_review_id, p_vendor_user_id, p_reason)
     483    RETURNING ticket_id INTO p_ticket_id;
     484END;
     485$$;
     486
     487CALL sp_file_dispute(NULL, 1000001, 55001, 'Оценките не го одразуваат договорениот обем на работа.');
     488}}}
     489
     490----
     491
     492=== Процедура 9: Решавање на спор (sp_resolve_dispute) ===
     493
     494Менаџмент корисник решава отворен спор: се доделува корисникот, се запишува белешката и спорот се означува како решен; непостоечки или веќе решен спор се одбива. Времето на решавање го пополнува тригерот за решавање на спор.
     495
     496{{{#!sql
     497CREATE OR REPLACE PROCEDURE sp_resolve_dispute(
     498    IN p_ticket_id                   int4,
     499    IN p_assigned_management_user_id int4,
     500    IN p_resolution_note             text
     501)
     502LANGUAGE plpgsql AS $$
     503BEGIN
     504    -- Белешката за решавање не смее да биде празна
     505    PERFORM fn_assert_not_blank(p_resolution_note, 'resolution_note');
     506
     507    -- Дека доделениот менаџмент корисник постои го проверува тригерот
     508    -- trg_dispute_ticket_resolve при UPDATE подолу, кој го запишува и
     509    -- времето на решавање (resolved_at); само отворен спор може да се реши
     510    UPDATE Dispute_Ticket
     511    SET    assigned_management_user_id = p_assigned_management_user_id,
     512           resolution_note             = p_resolution_note,
     513           is_resolved                 = true
     514    WHERE  ticket_id   = p_ticket_id
     515      AND  is_resolved = false;
     516
     517    IF NOT FOUND THEN
     518        RAISE EXCEPTION
     519            'Ticket % does not exist or is already resolved', p_ticket_id;
     520    END IF;
     521END;
     522$$;
     523
     524CALL sp_resolve_dispute(143895, 75001, 'Разгледано: оценките остануваат, рецензијата се објавува.');
     525}}}
     526
     527----
     528
     529=== Процедура 10: Објавување рецензија (sp_publish_review) ===
     530
     531Ја објавува рецензијата, по што таа влегува во јавната просечна оценка на софтверската агенција. Непостоечка или веќе објавена рецензија се одбива; рецензија со отворен спор ја одбива тригерот за објавување.
     532
     533{{{#!sql
     534CREATE OR REPLACE PROCEDURE sp_publish_review(
     535    IN p_review_id int4
     536)
     537LANGUAGE plpgsql AS $$
     538BEGIN
     539    -- Тригерот trg_review_publish_guard ја одбива промената додека
     540    -- рецензијата има отворен спор
     541    UPDATE Review
     542    SET    is_published = true
     543    WHERE  review_id    = p_review_id
     544      AND  is_published = false;
     545
     546    IF NOT FOUND THEN
     547        RAISE EXCEPTION
     548            'Review % does not exist or is already published', p_review_id;
     549    END IF;
     550END;
     551$$;
     552
     553CALL sp_publish_review(1000001);
     554}}}
     555
     556----
     557
     558=== Процедура 11: Обновување на претплата (sp_renew_vendor_subscription) ===
     559
     560Обновување на претплатата на софтверската агенција: тековниот активен период се затвора најдоцна на денот кога почнува новиот (порано зададен крај се задржува), а новиот период се отвора со избраното ниво. Агенцијата и нивото мора да постојат, а новиот период мора да почне по почетокот на тековниот; договорната цена ја проверува тригерот за цени на претплата.
     561
     562{{{#!sql
     563CREATE OR REPLACE PROCEDURE sp_renew_vendor_subscription(
     564    OUT p_new_contract_id      int4,  -- клучот на новиот период; contract_id е примарниот клуч на Vendor_Subscription
     565    IN  p_vendor_id            int4,
     566    IN  p_new_tier_id          int4,
     567    IN  p_new_start_date       date          DEFAULT CURRENT_DATE,
     568    IN  p_new_end_date         date          DEFAULT NULL,
     569    IN  p_new_negotiated_price numeric(10,2) DEFAULT NULL
     570)
     571LANGUAGE plpgsql AS $$
     572DECLARE
     573    v_current_start date;
     574BEGIN
     575    PERFORM fn_assert_vendor_exists(p_vendor_id);
     576
     577    -- Дека нивото постои и дека договорената цена одговара на неговата ценовна
     578    -- политика го проверува тригерот trg_vendor_subscription_pricing при INSERT подолу
     579
     580    IF p_new_start_date IS NULL THEN
     581        RAISE EXCEPTION 'start_date is required';
     582    END IF;
     583
     584    -- Крајниот датум, ако е даден, мора да биде по почетниот
     585    IF p_new_end_date IS NOT NULL AND p_new_end_date <= p_new_start_date THEN
     586        RAISE EXCEPTION
     587            'end_date (%) must be after start_date (%)', p_new_end_date, p_new_start_date;
     588    END IF;
     589
     590    -- Тековниот период се затвора на почетокот на новиот (end_date е
     591    -- ексклузивен; порано зададен крај се задржува), па новиот период мора да
     592    -- почне по почетокот на тековниот, инаку затворениот период би бил празен
     593    SELECT start_date
     594    INTO   v_current_start
     595    FROM   Vendor_Subscription
     596    WHERE  vendor_id = p_vendor_id
     597      AND  is_active = true;
     598
     599    IF v_current_start IS NOT NULL AND v_current_start >= p_new_start_date THEN
     600        RAISE EXCEPTION
     601            'The current period of vendor % began on %; the new period must start after that',
     602            p_vendor_id, v_current_start;
     603    END IF;
     604
     605    -- LEAST: отворен период добива крај; период што веќе истекол го задржува својот
     606    UPDATE Vendor_Subscription
     607    SET    is_active = false,
     608           end_date  = LEAST(end_date, p_new_start_date)
     609    WHERE  vendor_id = p_vendor_id
     610      AND  is_active = true;
     611
     612    INSERT INTO Vendor_Subscription (
     613        vendor_id, tier_id, negotiated_price, start_date, end_date
     614    )
     615    VALUES (
     616        p_vendor_id, p_new_tier_id, p_new_negotiated_price,
     617        p_new_start_date, p_new_end_date
     618    )
     619    RETURNING contract_id INTO p_new_contract_id;
     620END;
     621$$;
     622
     623CALL sp_renew_vendor_subscription(NULL, 42, 3, CURRENT_DATE, NULL, 1234.50);
     624}}}
     625
     626----
    20627
    21628== Функции ==
    22629
    23 Функциите главно се користат како помошни операции кои потоа се повикуваат од процедурите и тригерите.
    24 
    25 Едноставен пример е функцијата за добивање на целосното име на корисник:
     630Ниту една функција не запишува податоци; ги повикуваат процедурите и тригерите.
     631
     632=== Функција 1: Полно име на корисник (fn_get_full_name) ===
     633
     634Го враќа полното име на корисникот. Составувањето на името е на едно место, наместо да се повторува секаде каде што апликацијата прикажува автор на рецензија, поднесувач на спор или актер во историјата.
    26635
    27636{{{#!sql
    28637CREATE OR REPLACE FUNCTION fn_get_full_name(p_user_id int4)
    29 RETURNS text LANGUAGE sql STABLE AS $$
    30 SELECT first_name || ' ' || last_name
    31 FROM "User"
    32 WHERE user_id = p_user_id;
    33 
    34 $$$;
    35 }}}
    36 
    37 За добивање на vendor-от поврзан со даден проект се користи:
     638RETURNS text LANGUAGE sql STABLE STRICT AS $$
     639    -- STABLE: не менува податоци и за исти аргументи дава ист резултат во една наредба;
     640    -- STRICT: за NULL аргумент враќа NULL без извршување
     641    SELECT first_name || ' ' || last_name
     642    FROM "User"
     643    WHERE user_id = p_user_id;
     644$$;
     645}}}
     646
     647=== Функција 2: Софтверска агенција на проект (fn_get_vendor_id_for_project) ===
     648
     649Ја враќа софтверската агенција што го извршува проектот. Проектот не ја чува агенцијата директно, туку таа е страна на договорот, па патот преку договорот е напишан на едно место.
    38650
    39651{{{#!sql
    40652CREATE OR REPLACE FUNCTION fn_get_vendor_id_for_project(p_project_id int4)
    41 RETURNS int4 LANGUAGE sql STABLE AS $$
     653RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
    42654    SELECT cvc.vendor_id
    43655    FROM Project p
    44     JOIN Client_Vendor_Contract cvc
    45       ON cvc.contract_id = p.contract_id
     656    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
    46657    WHERE p.project_id = p_project_id;
    47658$$;
    48659}}}
    49660
    50 Поголемиот дел од функциите се validation функции. На пример:
     661=== Функција 3: Клиент на проект (fn_get_client_id_for_project) ===
     662
     663Го враќа клиентот на проектот, преку договорот, на ист начин. Двете функции се основата за проверките на припадност подолу.
     664
     665{{{#!sql
     666CREATE OR REPLACE FUNCTION fn_get_client_id_for_project(p_project_id int4)
     667RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
     668    SELECT cvc.client_id
     669    FROM Project p
     670    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
     671    WHERE p.project_id = p_project_id;
     672$$;
     673}}}
     674
     675=== Функција 4: Корисник на софтверската агенција на проектот (fn_is_vendor_user_of_project) ===
     676
     677Правилото „корисник на софтверска агенција може да оспори рецензија или да запише промена на статус само за проект на својата агенција“: дали корисникот припаѓа на агенцијата што го извршува проектот. Се користи при поднесување спор и при запишување во историјата на статуси.
     678
     679{{{#!sql
     680CREATE OR REPLACE FUNCTION fn_is_vendor_user_of_project(p_user_id int4, p_project_id int4)
     681RETURNS bool LANGUAGE sql STABLE STRICT AS $$
     682    SELECT vu.vendor_id = fn_get_vendor_id_for_project(p_project_id)
     683    FROM Vendor_User vu
     684    WHERE vu.user_id = p_user_id;
     685$$;
     686}}}
     687
     688=== Функција 5: Корисник на клиентот на проектот (fn_is_client_user_of_project) ===
     689
     690Правилото „само клиентот на проектот може да го рецензира“: дали корисникот припаѓа на клиентот од договорот на проектот. Се користи при поднесување рецензија.
     691
     692{{{#!sql
     693CREATE OR REPLACE FUNCTION fn_is_client_user_of_project(p_user_id int4, p_project_id int4)
     694RETURNS bool LANGUAGE sql STABLE STRICT AS $$
     695    SELECT cu.client_id = fn_get_client_id_for_project(p_project_id)
     696    FROM Client_User cu
     697    WHERE cu.user_id = p_user_id;
     698$$;
     699}}}
     700
     701=== Функција 6: Проверка за непразен текст (fn_assert_not_blank) ===
     702
     703Одбива празен текст (и текст само од празни места) со порака што го именува полето. Ја користат процедурите за сите задолжителни текстуални полиња: имиња, лозинка, наслов на договор, име на проект, резиме на рецензија, причина и белешка на спор. Така апликацијата добива разбирлива порака наместо грешката на ограничувањето.
     704
     705{{{#!sql
     706CREATE OR REPLACE FUNCTION fn_assert_not_blank(p_value text, p_name text)
     707RETURNS void LANGUAGE plpgsql AS $$
     708BEGIN
     709    IF btrim(coalesce(p_value, '')) = '' THEN
     710        RAISE EXCEPTION '% must not be blank', p_name;
     711    END IF;
     712END;
     713$$;
     714}}}
     715
     716=== Функција 7: Проверка за позитивен износ (fn_assert_positive) ===
     717
     718Одбива износ што не е позитивен, со порака што ја именува вредноста. Ја користат процедурите што запишуваат буџет при склучување договор и при промена на буџет.
     719
     720{{{#!sql
     721CREATE OR REPLACE FUNCTION fn_assert_positive(p_value numeric, p_name text)
     722RETURNS void LANGUAGE plpgsql AS $$
     723BEGIN
     724    IF p_value IS NULL OR p_value <= 0 THEN
     725        RAISE EXCEPTION '% must be greater than zero (received: %)', p_name, p_value;
     726    END IF;
     727END;
     728$$;
     729}}}
     730
     731=== Функции 8–18: Проверки за постоење (fn_assert_*_exists) ===
     732
     733Единаесет функции со иста форма, по една за секоја табела на која упатуваат процедурите и тригерите. Тие го проверуваат истото што и надворешниот клуч, но пред запишувањето и со описна порака. Проверката за проект:
    51734
    52735{{{#!sql
    … …  
    54737RETURNS void LANGUAGE plpgsql AS $$
    55738BEGIN
    56     IF NOT EXISTS (
    57         SELECT 1
    58         FROM Project
    59         WHERE project_id = p_project_id
    60     ) THEN
     739    IF NOT EXISTS (SELECT 1 FROM Project WHERE project_id = p_project_id) THEN
    61740        RAISE EXCEPTION 'Project % does not exist', p_project_id;
    62741    END IF;
    … …  
    65744}}}
    66745
    67 На истиот принцип се имплементирани проверки за `Vendor`, `Client`, `Review`, `Project_Status`, `Subscription_Tier`, корисничките типови и `Rating_Dimension`.
    68 
    69 == Процедури ==
    70 
    71 Процедурите ги имплементираат главните операции кои менуваат податоци во повеќе поврзани табели.
    72 
    73 === Регистрација на корисник ===
    74 
    75 `sp_register_user` прво креира запис во `"User"`, а потоа го додава корисникот во соодветната subtype табела.
    76 
    77 {{{#!sql
    78 INSERT INTO "User" (
    79     type, first_name, last_name, email, password_hash
    80 )
    81 VALUES (
    82     p_type, p_first_name, p_last_name,
    83     p_email, p_password_hash
    84 )
    85 RETURNING user_id INTO p_user_id;
    86 
    87 IF p_type = 'client' THEN
    88     INSERT INTO Client_User (user_id, client_id)
    89     VALUES (p_user_id, p_client_id);
    90 
    91 ELSIF p_type = 'vendor' THEN
    92     INSERT INTO Vendor_User (user_id, vendor_id)
    93     VALUES (p_user_id, p_vendor_id);
    94 
    95 ELSIF p_type = 'management' THEN
    96     INSERT INTO Management_User (user_id, role_id)
    97     VALUES (p_user_id, p_role_id);
    98 END IF;
    99 }}}
    100 
    101 Пред внесувањето се проверуваат типот на корисникот, задолжителните параметри и референците кон останатите табели.
    102 
    103 === Креирање договор и проект ===
    104 
    105 `sp_create_contract_with_project` овозможува со една операција да се креира договор и неговиот почетен проект:
    106 
    107 {{{#!sql
    108 INSERT INTO Client_Vendor_Contract (
    109     client_id, vendor_id, contract_title,
    110     contract_number, start_date, end_date,
    111     total_value, currency_code, terms_summary
    112 )
    113 VALUES (
    114     p_client_id, p_vendor_id, p_contract_title,
    115     p_contract_number, p_cvc_start_date, p_cvc_end_date,
    116     p_total_value, p_currency_code, p_terms_summary
    117 )
    118 RETURNING contract_id INTO p_contract_id;
    119 
    120 INSERT INTO Project (
    121     contract_id, status_id, project_name,
    122     start_date, end_date, budget
    123 )
    124 VALUES (
    125     p_contract_id, p_status_id, p_project_name,
    126     p_project_start_date, p_project_end_date, p_budget
    127 )
    128 RETURNING project_id INTO p_project_id;
    129 }}}
    130 
    131 Пред креирањето се проверуваат client, vendor и project status, како и буџетот и потребните текстуални полиња.
    132 
    133 === Промена на статус на проект ===
    134 
    135 `sp_update_project_status` го менува тековниот статус и истовремено додава запис во историјата:
    136 
    137 {{{#!sql
    138 UPDATE Project
    139 SET status_id = p_new_status_id
    140 WHERE project_id = p_project_id;
    141 
    142 INSERT INTO Project_Status_History (
    143     project_id,
    144     status_id,
    145     vendor_user_id,
    146     management_user_id,
    147     comment
    148 )
    149 VALUES (
    150     p_project_id,
    151     p_new_status_id,
    152     p_vendor_user_id,
    153     p_management_user_id,
    154     p_comment
    155 );
    156 }}}
    157 
    158 Со ова секоја промена на статусот останува евидентирана.
    159 
    160 === Внесување review ===
    161 
    162 `sp_submit_review` креира review и неговите оценки во една операција.
    163 
    164 Оценките се предаваат како JSONB низа:
    165 
    166 {{{#!sql
    167 FOR v_score IN
    168     SELECT * FROM jsonb_array_elements(p_scores)
    169 LOOP
    170     PERFORM fn_assert_dimension_exists(
    171         (v_score->>'dimension_id')::int4
    172     );
    173 
    174     IF ((v_score->>'score_value')::int4 < 1)
    175        OR ((v_score->>'score_value')::int4 > 5) THEN
    176         RAISE EXCEPTION
    177             'Score value must be between 1 and 5';
    178     END IF;
    179 
    180     INSERT INTO Review_Score (
    181         review_id,
    182         dimension_id,
    183         score_value
    184     )
    185     VALUES (
    186         p_review_id,
    187         (v_score->>'dimension_id')::int4,
    188         (v_score->>'score_value')::int4
    189     );
    190 END LOOP;
    191 }}}
    192 
    193 Процедурата дополнително проверува дали корисникот припаѓа на клиентот на проектот и дали за проектот веќе постои review.
    194 
    195 Останатите процедури се користат за:
    196 
    197  * деактивирање на корисник
    198  * промена на project budget
    199  * отворање dispute ticket
    200  * разрешување dispute ticket
    201  * објавување review
    202  * обновување vendor subscription
     746Останатите десет се `fn_assert_vendor_exists`, `fn_assert_review_exists`, `fn_assert_client_exists`, `fn_assert_status_exists`, `fn_assert_tier_exists`, `fn_assert_role_exists`, `fn_assert_client_user_exists`, `fn_assert_vendor_user_exists`, `fn_assert_management_user_exists` и `fn_assert_dimension_exists`.
     747
     748----
    203749
    204750== Тригери ==
    205751
    206 Тригерите автоматски извршуваат проверки или дополнителни операции при `INSERT` и `UPDATE`.
    207 
    208 === User subtype проверка ===
    209 
    210 За `Client_User`, `Vendor_User` и `Management_User` се користи заедничка trigger функција која спречува еден корисник да припаѓа на повеќе subtype табели.
    211 
    212 {{{#!sql
    213 CREATE TRIGGER trg_client_user_subtype
    214   BEFORE INSERT ON Client_User
     752=== Тригери 1–3: Подтип на корисник (trg_enforce_user_subtype) ===
     753
     754Редица во `Client_User`, `Vendor_User` или `Management_User` се прифаќа само ако типот на корисникот во `"User"` е соодветно `client`, `vendor` или `management`, така што ниту еден корисник не може да биде во табела на туѓ подтип. Една функција служи за трите табели.
     755
     756{{{#!sql
     757CREATE OR REPLACE FUNCTION trg_enforce_user_subtype()
     758RETURNS TRIGGER LANGUAGE plpgsql AS $$
     759DECLARE
     760  v_expected text;
     761  v_actual   text;
     762BEGIN
     763  -- TG_TABLE_NAME е името на табелата што го активирала тригерот (со мали букви);
     764  -- NEW е редицата што се вметнува или менува
     765  v_expected := CASE TG_TABLE_NAME
     766                  WHEN 'client_user'     THEN 'client'
     767                  WHEN 'vendor_user'     THEN 'vendor'
     768                  WHEN 'management_user' THEN 'management'
     769                  ELSE NULL
     770                END;
     771
     772  IF v_expected IS NULL THEN
     773    RAISE EXCEPTION 'trg_enforce_user_subtype is not valid on table %', TG_TABLE_NAME;
     774  END IF;
     775
     776  SELECT type INTO v_actual FROM "User" WHERE user_id = NEW.user_id;
     777
     778  IF NOT FOUND THEN
     779    RAISE EXCEPTION 'User % does not exist', NEW.user_id;
     780  END IF;
     781
     782  IF v_actual <> v_expected THEN
     783    RAISE EXCEPTION 'User % type must be % (it is %)', NEW.user_id, v_expected, v_actual;
     784  END IF;
     785
     786  RETURN NEW;  -- редицата се пропушта; RAISE EXCEPTION погоре ја одбива
     787END;
     788$$;
     789
     790CREATE OR REPLACE TRIGGER trg_client_user_subtype
     791  BEFORE INSERT OR UPDATE ON Client_User
     792  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();
     793
     794CREATE OR REPLACE TRIGGER trg_vendor_user_subtype
     795  BEFORE INSERT OR UPDATE ON Vendor_User
     796  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();
     797
     798CREATE OR REPLACE TRIGGER trg_management_user_subtype
     799  BEFORE INSERT OR UPDATE ON Management_User
     800  FOR EACH ROW EXECUTE FUNCTION trg_enforce_user_subtype();
     801}}}
     802
     803=== Тригер 4: Непроменлив тип на корисник (trg_prevent_user_type_change) ===
     804
     805Типот на корисникот е фиксен од регистрацијата: промена би ја оставила редицата на стариот подтип во несогласност со новиот тип.
     806
     807{{{#!sql
     808CREATE OR REPLACE FUNCTION trg_prevent_user_type_change()
     809RETURNS TRIGGER LANGUAGE plpgsql AS $$
     810BEGIN
     811  -- OLD е редицата пред промената; тригерот се повикува само кога типот се менува (WHEN)
     812  RAISE EXCEPTION 'User % type must stay % and cannot be changed', OLD.user_id, OLD.type;
     813END;
     814$$;
     815
     816CREATE OR REPLACE TRIGGER trg_user_type_immutable
     817  BEFORE UPDATE OF type ON "User"
    215818  FOR EACH ROW
    216   EXECUTE FUNCTION trg_enforce_user_subtype();
    217 
    218 CREATE TRIGGER trg_vendor_user_subtype
    219   BEFORE INSERT ON Vendor_User
    220   FOR EACH ROW
    221   EXECUTE FUNCTION trg_enforce_user_subtype();
    222 
    223 CREATE TRIGGER trg_management_user_subtype
    224   BEFORE INSERT ON Management_User
    225   FOR EACH ROW
    226   EXECUTE FUNCTION trg_enforce_user_subtype();
    227 }}}
    228 
    229 Дополнително се проверува дали вредноста на `"User".type` одговара на subtype табелата.
    230 
    231 === Автоматско ажурирање на updated_at ===
    232 
    233 За табелите кои имаат `updated_at` е дефинирана заедничка trigger функција:
     819  WHEN (NEW.type <> OLD.type)
     820  EXECUTE FUNCTION trg_prevent_user_type_change();
     821}}}
     822
     823=== Тригер 5: Договорна цена на претплата (trg_enforce_negotiated_price) ===
     824
     825Ценовната политика на нивоата: претплата на ниво со договорна цена мора да има договорена цена, а претплата на ниво со фиксна цена не смее да има.
     826
     827{{{#!sql
     828CREATE OR REPLACE FUNCTION trg_enforce_negotiated_price()
     829RETURNS TRIGGER LANGUAGE plpgsql AS $$
     830DECLARE
     831  v_allows_custom bool;
     832BEGIN
     833  -- Нивото мора да постои пред проверката на ценовната политика
     834  PERFORM fn_assert_tier_exists(NEW.tier_id);
     835
     836  SELECT allows_custom_pricing
     837  INTO v_allows_custom
     838  FROM Subscription_Tier
     839  WHERE tier_id = NEW.tier_id;
     840
     841  IF v_allows_custom AND NEW.negotiated_price IS NULL THEN
     842    RAISE EXCEPTION
     843      'negotiated_price is required for tiers with custom pricing (contract_id: %)', NEW.contract_id;
     844  END IF;
     845
     846  IF NOT v_allows_custom AND NEW.negotiated_price IS NOT NULL THEN
     847    RAISE EXCEPTION
     848      'negotiated_price must be NULL for fixed-price tiers (contract_id: %)', NEW.contract_id;
     849  END IF;
     850
     851  RETURN NEW;
     852END;
     853$$;
     854
     855CREATE OR REPLACE TRIGGER trg_vendor_subscription_pricing
     856  BEFORE INSERT OR UPDATE ON Vendor_Subscription
     857  FOR EACH ROW EXECUTE FUNCTION trg_enforce_negotiated_price();
     858}}}
     859
     860=== Тригери 6–12: Време на последна промена (trg_set_updated_at) ===
     861
     862Колоната `updated_at` се пополнува автоматски при секоја промена на редица во седумте табели што ја имаат, така што апликацијата не мора да ја поставува.
    234863
    235864{{{#!sql
    … …  
    237866RETURNS TRIGGER LANGUAGE plpgsql AS $$
    238867BEGIN
     868  -- BEFORE UPDATE: промената на NEW се запишува во редицата
    239869  NEW.updated_at := NOW();
    240870  RETURN NEW;
    241871END;
    242872$$;
    243 }}}
    244 
    245 На пример:
    246 
    247 {{{#!sql
    248 CREATE TRIGGER trg_project_updated_at
     873
     874CREATE OR REPLACE TRIGGER trg_project_updated_at
    249875  BEFORE UPDATE ON Project
    250   FOR EACH ROW
    251   EXECUTE FUNCTION trg_set_updated_at();
    252 }}}
    253 
    254 Истиот механизам се користи и за договори, subscriptions, reviews, dispute tickets и корисници.
    255 
    256 === Историја на промена на буџет ===
    257 
    258 При промена на буџетот на проект, автоматски се креира audit запис:
     876  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     877
     878CREATE OR REPLACE TRIGGER trg_vendor_subscription_updated_at
     879  BEFORE UPDATE ON Vendor_Subscription
     880  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     881
     882CREATE OR REPLACE TRIGGER trg_cvc_updated_at
     883  BEFORE UPDATE ON Client_Vendor_Contract
     884  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     885
     886CREATE OR REPLACE TRIGGER trg_pba_updated_at
     887  BEFORE UPDATE ON Project_Budget_Audit
     888  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     889
     890CREATE OR REPLACE TRIGGER trg_review_updated_at
     891  BEFORE UPDATE ON Review
     892  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     893
     894CREATE OR REPLACE TRIGGER trg_dispute_ticket_updated_at
     895  BEFORE UPDATE ON Dispute_Ticket
     896  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     897
     898CREATE OR REPLACE TRIGGER trg_user_updated_at
     899  BEFORE UPDATE ON "User"
     900  FOR EACH ROW EXECUTE FUNCTION trg_set_updated_at();
     901}}}
     902
     903=== Тригер 13: Ревизија на буџетот (trg_capture_budget_change) ===
     904
     905Историјата на буџетот: секоја промена на буџетот на проект запишува редица со стариот и новиот износ во `Project_Budget_Audit`, без разлика дали доаѓа од процедура или од директна промена.
    259906
    260907{{{#!sql
    … …  
    262909RETURNS TRIGGER LANGUAGE plpgsql AS $$
    263910BEGIN
    264   IF NEW.budget IS DISTINCT FROM OLD.budget THEN
    265     INSERT INTO Project_Budget_Audit (
    266         project_id,
    267         old_budget,
    268         new_budget
    269     )
    270     VALUES (
    271         OLD.project_id,
    272         OLD.budget,
    273         NEW.budget
    274     );
    275   END IF;
    276 
     911  INSERT INTO Project_Budget_Audit (project_id, old_budget, new_budget)
     912  VALUES (OLD.project_id, OLD.budget, NEW.budget);
    277913  RETURN NEW;
    278914END;
    279915$$;
    280 }}}
    281 
    282 Тригерот се активира после промена на `Project`:
    283 
    284 {{{#!sql
    285 CREATE TRIGGER trg_project_budget_audit
     916
     917CREATE OR REPLACE TRIGGER trg_project_budget_audit
    286918  AFTER UPDATE ON Project
    287919  FOR EACH ROW
     920  -- IS DISTINCT FROM ги споредува и NULL вредностите како обични вредности
     921  WHEN (OLD.budget IS DISTINCT FROM NEW.budget)
    288922  EXECUTE FUNCTION trg_capture_budget_change();
    289923}}}
    290924
    291 === Проверка на dispute и review ===
    292 
    293 При разрешување на dispute се проверува дали е назначен management корисник. Доколку `resolved_at` не е поставен, автоматски се поставува тековното време.
    294 
    295 {{{#!sql
    296 IF NEW.is_resolved = true AND OLD.is_resolved = false THEN
     925=== Тригер 14: Актер во историјата на статуси (trg_validate_status_history_actor) ===
     926
     927Промена на статус може да запише само корисник на софтверската агенција што го извршува проектот или менаџмент корисник; корисник на друга агенција не може да менува туѓ проект. Тригерот проверува и дека проектот и актерот постојат, па процедурата за промена на статус не го повторува тоа.
     928
     929{{{#!sql
     930CREATE OR REPLACE FUNCTION trg_validate_status_history_actor()
     931RETURNS TRIGGER LANGUAGE plpgsql AS $$
     932BEGIN
     933  -- Проектот мора да постои пред проверката на актерот
     934  PERFORM fn_assert_project_exists(NEW.project_id);
     935
     936  -- Актерот од агенцијата мора да постои и да ѝ припаѓа на агенцијата на проектот
     937  IF NEW.vendor_user_id IS NOT NULL THEN
     938    PERFORM fn_assert_vendor_user_exists(NEW.vendor_user_id);
     939
     940    IF NOT coalesce(fn_is_vendor_user_of_project(NEW.vendor_user_id, NEW.project_id), false) THEN
     941      RAISE EXCEPTION
     942        'vendor_user % does not belong to the vendor on project %',
     943        NEW.vendor_user_id, NEW.project_id;
     944    END IF;
     945  END IF;
     946
     947  -- Менаџмент корисникот, ако е актер, мора да постои
     948  IF NEW.management_user_id IS NOT NULL THEN
     949    PERFORM fn_assert_management_user_exists(NEW.management_user_id);
     950  END IF;
     951
     952  RETURN NEW;
     953END;
     954$$;
     955
     956CREATE OR REPLACE TRIGGER trg_status_history_actor
     957  BEFORE INSERT ON Project_Status_History
     958  FOR EACH ROW EXECUTE FUNCTION trg_validate_status_history_actor();
     959}}}
     960
     961=== Тригер 15: Преклопување на претплати (trg_prevent_subscription_overlap) ===
     962
     963Периодите на претплата на една софтверска агенција никогаш не се преклопуваат; крајниот датум е ексклузивен, па нов период може да почне на денот кога завршува претходниот.
     964
     965{{{#!sql
     966CREATE OR REPLACE FUNCTION trg_prevent_subscription_overlap()
     967RETURNS TRIGGER LANGUAGE plpgsql AS $$
     968BEGIN
     969  -- Два периода се преклопуваат ако секој почнува пред крајот на другиот;
     970  -- NULL end_date е отворен период. contract_id <> NEW.contract_id ја
     971  -- исклучува самата редица при UPDATE
     972  IF EXISTS (
     973    SELECT 1 FROM Vendor_Subscription
     974     WHERE vendor_id    = NEW.vendor_id
     975       AND contract_id <> NEW.contract_id
     976       AND (NEW.end_date IS NULL OR start_date < NEW.end_date)
     977       AND (end_date    IS NULL OR end_date    > NEW.start_date)
     978  ) THEN
     979    RAISE EXCEPTION
     980      'Vendor % already has a subscription period overlapping % - %',
     981      NEW.vendor_id, NEW.start_date, coalesce(NEW.end_date::text, 'open');
     982  END IF;
     983
     984  RETURN NEW;
     985END;
     986$$;
     987
     988CREATE OR REPLACE TRIGGER trg_vendor_subscription_overlap
     989  BEFORE INSERT OR UPDATE ON Vendor_Subscription
     990  FOR EACH ROW EXECUTE FUNCTION trg_prevent_subscription_overlap();
     991}}}
     992
     993=== Тригер 16: Решавање на спор (trg_validate_dispute_resolution) ===
     994
     995Спор може да се реши само ако има доделен менаџмент корисник; времето на решавање се пополнува автоматски, а решен спор не може повторно да се отвори.
     996
     997{{{#!sql
     998CREATE OR REPLACE FUNCTION trg_validate_dispute_resolution()
     999RETURNS TRIGGER LANGUAGE plpgsql AS $$
     1000BEGIN
     1001  -- Премин од отворен во решен спор
     1002  IF NEW.is_resolved = true AND OLD.is_resolved = false THEN
    2971003    IF NEW.assigned_management_user_id IS NULL THEN
    298         RAISE EXCEPTION
    299           'Dispute ticket % must have an assigned management user before it can be resolved',
    300           OLD.ticket_id;
    301     END IF;
     1004      RAISE EXCEPTION
     1005        'Dispute ticket % must have an assigned management user before it can be resolved', OLD.ticket_id;
     1006    END IF;
     1007
     1008    -- Доделениот менаџмент корисник мора да постои
     1009    PERFORM fn_assert_management_user_exists(NEW.assigned_management_user_id);
    3021010
    3031011    IF NEW.resolved_at IS NULL THEN
    304         NEW.resolved_at := NOW();
    305     END IF;
    306 END IF;
    307 }}}
    308 
    309 Дополнително, review не може да биде објавен додека има неразрешен dispute:
    310 
    311 {{{#!sql
    312 IF EXISTS (
    313     SELECT 1
    314     FROM Dispute_Ticket
    315     WHERE review_id = NEW.review_id
    316       AND is_resolved = false
    317 ) THEN
    318     RAISE EXCEPTION
    319       'Review % cannot be published while it has unresolved dispute tickets',
    320       NEW.review_id;
    321 END IF;
    322 }}}
    323 
    324 == Заклучок ==
    325 
    326 Функциите се користат за повторно употребливи проверки и помошни операции, процедурите ги имплементираат главните операции над податоците, а тригерите автоматски ги применуваат правилата кои треба да важат независно од начинот на кој се менуваат податоците.
    327 
    328 Со ова дел од апликациската и validation логиката е имплементирана директно во PostgreSQL базата.
    329 $$$
     1012      NEW.resolved_at := NOW();
     1013    END IF;
     1014  END IF;
     1015
     1016  -- Обратниот премин е забранет
     1017  IF OLD.is_resolved = true AND NEW.is_resolved = false THEN
     1018    RAISE EXCEPTION 'Resolved dispute ticket % cannot be re-opened', OLD.ticket_id;
     1019  END IF;
     1020
     1021  RETURN NEW;
     1022END;
     1023$$;
     1024
     1025CREATE OR REPLACE TRIGGER trg_dispute_ticket_resolve
     1026  BEFORE UPDATE ON Dispute_Ticket
     1027  FOR EACH ROW EXECUTE FUNCTION trg_validate_dispute_resolution();
     1028}}}
     1029
     1030=== Тригер 17: Објавување на оспорена рецензија (trg_prevent_publishing_disputed_review) ===
     1031
     1032Рецензија не може да се објави додека има отворен спор, што е смислата на системот за спорови: софтверската агенција ја оспорува рецензијата пред таа да стане јавна.
     1033
     1034{{{#!sql
     1035CREATE OR REPLACE FUNCTION trg_prevent_publishing_disputed_review()
     1036RETURNS TRIGGER LANGUAGE plpgsql AS $$
     1037BEGIN
     1038  -- Премин од необјавена во објавена рецензија
     1039  IF NEW.is_published = true AND OLD.is_published = false THEN
     1040    IF EXISTS (
     1041      SELECT 1 FROM Dispute_Ticket
     1042       WHERE review_id   = NEW.review_id
     1043         AND is_resolved = false
     1044    ) THEN
     1045      RAISE EXCEPTION
     1046        'Review % cannot be published while it has unresolved dispute tickets', NEW.review_id;
     1047    END IF;
     1048  END IF;
     1049
     1050  RETURN NEW;
     1051END;
     1052$$;
     1053
     1054CREATE OR REPLACE TRIGGER trg_review_publish_guard
     1055  BEFORE UPDATE ON Review
     1056  FOR EACH ROW EXECUTE FUNCTION trg_prevent_publishing_disputed_review();
     1057}}}
     1058
     1059=== Тригер 18: Рецензија само за завршен проект (trg_require_finished_project) ===
     1060
     1061Рецензијата е оценка на завршен ангажман: рецензија може да се запише само за проект со статус Completed или Cancelled, а не за проект во тек. Правилото чита друга табела (`Project`), па не може да биде CHECK ограничување; тригерот важи и за директно вметнување, а процедурата за поднесување рецензија се потпира на него.
     1062
     1063{{{#!sql
     1064CREATE OR REPLACE FUNCTION trg_require_finished_project()
     1065RETURNS TRIGGER LANGUAGE plpgsql AS $$
     1066DECLARE
     1067  v_status text;
     1068BEGIN
     1069  -- NEW.project_id е проектот на рецензијата што се вметнува
     1070  SELECT ps.status_name
     1071  INTO v_status
     1072  FROM Project p
     1073  JOIN Project_Status ps ON ps.status_id = p.status_id
     1074  WHERE p.project_id = NEW.project_id;
     1075
     1076  IF NOT FOUND THEN
     1077    RAISE EXCEPTION 'Project % does not exist', NEW.project_id;
     1078  END IF;
     1079
     1080  IF v_status NOT IN ('Completed', 'Cancelled') THEN
     1081    RAISE EXCEPTION 'Project % is %; a review needs a finished project', NEW.project_id, v_status;
     1082  END IF;
     1083
     1084  RETURN NEW;
     1085END;
     1086$$;
     1087
     1088-- и при промена на project_id на постоечка рецензија
     1089CREATE OR REPLACE TRIGGER trg_review_finished_project
     1090  BEFORE INSERT OR UPDATE OF project_id ON Review
     1091  FOR EACH ROW EXECUTE FUNCTION trg_require_finished_project();
     1092}}}