Фаза 4: Функции, процедури и тригери

Во оваа фаза се надоградуваме со функции и процедури што ќе помогнат дел од честите операции во базата-отворање записник, додавање прекршок, наплата на казна, затворање записник по платена казна, додавање категорија на возачка-да се извршуваат преку еден повик, со сите потребни проверки веќе вградени во нив. Исто така искористивме и тригери за работите што треба да се случуваат автоматски, како пресметката на вкупниот износ во уплатата и строга контрола на интегритетот, без рачно ажурирање.
programming.sql​

1. Тригери

Тригер 1: trigger_azuriraj_iznos_uplata

Секој пат кога ќе се додаде, измени или избрише ставка во Stavka_Zapisnik, тригерот ја пресметува вкупната сума на сите казни за тој записник и ја запишува во соодветната уплата. Така износот во Uplata секогаш е точен и не мора рачно да го ажурираме.

CREATE OR REPLACE FUNCTION azuriraj_iznos_uplata()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_id_zapisnik int;
v_nov_iznos   numeric(10,2);
BEGIN
IF TG_OP = 'DELETE' THEN
v_id_zapisnik := OLD.id_na_zapisnik;
ELSE
v_id_zapisnik := NEW.id_na_zapisnik;
END IF;

SELECT COALESCE(SUM(k.iznos_kazna), 0)
INTO v_nov_iznos
FROM Stavka_Zapisnik sz
JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
JOIN Kazna    k ON p.id_kazna        = k.id_kazna
WHERE sz.id_na_zapisnik = v_id_zapisnik;

UPDATE Uplata
SET iznos = v_nov_iznos
WHERE id_zapisnik = v_id_zapisnik
  AND status = 'Neplateno';

IF TG_OP = 'DELETE' THEN
    RETURN OLD;
ELSE
    RETURN NEW;
END IF;
END;
$$;

CREATE OR REPLACE TRIGGER trigger_azuriraj_iznos_uplata
AFTER INSERT OR UPDATE OR DELETE ON Stavka_Zapisnik
FOR EACH ROW
EXECUTE FUNCTION azuriraj_iznos_uplata();

Тригер 2: trigger_proverka_raboti_vo

Овој тригер се извршува пред секое вметнување или измена на податоците во табелата Raboti_vo со цел да се зачува бизнис логиката на системот. Тој гарантира дека полицаецот кој се доделува навистина постои во базата и дека датумот на започнување со работа не е празен. Дополнително, тригерот спречува еден полициски службеник да има активен работен однос (каде datum_do IS NULL) во повеќе од една полициска станица истовремено.

CREATE OR REPLACE FUNCTION proverka_raboti_vo_integritet()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = NEW.EMBG_P) THEN
        RAISE EXCEPTION 'Policaecot so EMBG % ne postoi vo sistemot!', NEW.EMBG_P;
    END IF;
 
    IF NEW.datum_od IS NULL THEN
        RAISE EXCEPTION 'Pocetokot na rabotniot odnos (datum_od) mora da bide definiran!';
    END IF;
 
    IF NEW.datum_do IS NULL THEN
        IF EXISTS (
            SELECT 1 FROM Raboti_vo 
            WHERE EMBG_P = NEW.EMBG_P AND datum_do IS NULL AND id_stanica <> NEW.id_stanica
        ) THEN
            RAISE EXCEPTION 'Policaecot so EMBG % vekje ima aktiven angazhman vo druga stanica!', NEW.EMBG_P;
        END IF;
    END IF;
 
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trigger_proverka_raboti_vo
    BEFORE INSERT OR UPDATE ON Raboti_vo
    FOR EACH ROW
    EXECUTE FUNCTION proverka_raboti_vo_integritet();

Тригер 3: trigger_proverka_registracija

Овој тригер е задолжен за проверка на интегритетот на податоците пред да се изврши вметнување или ажурирање во табелата Registracija.Registracijaтаа референцира Sopstvenost (преку id_sopstvenost) и Registerska_tablica (преку id_tablica). Затоа тригерот прави валидација дека сопственоста постои и дека, доколку е доделена табличка, таа табличка постои. Како клучен дел од валидацијата, тригерот користи регуларен израз (REGEX) врз составениот запис на табличката (регион-број-код) за да осигура дека таа го следи официјалниот македонски стандард (на пр.SK-1234-AA).

CREATE OR REPLACE FUNCTION proverka_registracija_integritet()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
v_tablica text;
BEGIN
IF NOT EXISTS (SELECT 1 FROM Sopstvenost WHERE id_sopstvenost = NEW.id_sopstvenost) THEN
RAISE EXCEPTION 'Sopstvenosta so id % ne postoi vo sistemot!', NEW.id_sopstvenost;
END IF;

IF NEW.id_tablica IS NOT NULL THEN
    SELECT region || '-' || broj || '-' || kod
    INTO v_tablica
    FROM Registerska_tablica
    WHERE id_tablica = NEW.id_tablica;

    IF v_tablica IS NULL THEN
        RAISE EXCEPTION 'Registerska tablica so id % ne postoi!', NEW.id_tablica;
    END IF;

    IF NOT (UPPER(v_tablica) ~ '^[A-Z]{2,3}-[0-9]{3,4}-[A-Z]{2}$') THEN
        RAISE EXCEPTION 'Registerskara tablica "%" ne e vo validen format! Очекуван формат: ZZ-0000-ZZ', v_tablica;
    END IF;
END IF;

RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER trigger_proverka_registracija
BEFORE INSERT OR UPDATE ON Registracija
FOR EACH ROW
EXECUTE FUNCTION proverka_registracija_integritet();

2. Процедури

Процедура 1: kreiraj_zapisnik_so_prekrsok

Отвора нов записник со прв прекршок и веднаш креира празна уплата со статус 'Neplateno'. Записникот се отвора со status_zapisnik = 'Otvoren' (преку default). Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.

CREATE OR REPLACE PROCEDURE kreiraj_zapisnik_so_prekrsok(
    p_embg_prekrsuvac char(13),
    p_broj_sasija     varchar(17),
    p_embg_policaec   char(13),
    p_lokacija        varchar(100),
    p_id_slucaj       int,
    p_id_prekrsok     int,
    p_vreme           time    DEFAULT CURRENT_TIME,
    p_datum           date    DEFAULT CURRENT_DATE,
    p_potpis          boolean DEFAULT false
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_id_zapisnik int;
BEGIN
 
    IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = p_embg_prekrsuvac) THEN
        RAISE EXCEPTION 'Gragjaninot so EMBG % ne postoi!', p_embg_prekrsuvac;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Policaec WHERE EMBG_P = p_embg_policaec) THEN
        RAISE EXCEPTION 'Policaecot so EMBG % ne postoi!', p_embg_policaec;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = p_broj_sasija) THEN
        RAISE EXCEPTION 'Voziloto so broj na sasija % ne postoi!', p_broj_sasija;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
        RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
    END IF;
 
    IF NOT EXISTS (SELECT 1 FROM Slucaj WHERE id_slucaj = p_id_slucaj) THEN
        RAISE EXCEPTION 'Slucajot so id % ne postoi!', p_id_slucaj;
    END IF;
 
 
    INSERT INTO Zapisnik (
        vreme, datum, lokacija, Potpis,
        id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec
    )
    VALUES (
        p_vreme, p_datum, p_lokacija, p_potpis,
        p_id_slucaj, p_embg_prekrsuvac, p_broj_sasija, p_embg_policaec
    )
    RETURNING id_na_zapisnik INTO v_id_zapisnik;
 
 
    INSERT INTO Uplata (iznos, status, Uplatil_Gragjanin, id_zapisnik)
    VALUES (0, 'Neplateno', p_embg_prekrsuvac, v_id_zapisnik);
 
 
    INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
    VALUES (1, v_id_zapisnik, p_id_prekrsok);
 
    RAISE NOTICE 'Kreiran zapisnik so id % so prv prekrsok %.', v_id_zapisnik, p_id_prekrsok;
END;
$$;

$$;

Повик:

CALL kreiraj_zapisnik_so_prekrsok(
'1508004480145', 'CE8C5F1D5AA4C0871', '1609966470237',
'Bulevar Partizanski Odredi', 1, 1
);

Процедура 2: dodadi_stavka_zapisnik

Додава нов прекршок (ставка) на веќе постоечки записник и сам го одредува следниот реден број. Не дозволува додавање ако записникот е веќе платен.

CREATE OR REPLACE PROCEDURE dodadi_stavka_zapisnik(
p_id_zapisnik int,
p_id_prekrsok int
)
LANGUAGE plpgsql
AS $$
DECLARE
v_reden_broj     int;
v_status_uplata  varchar(30);
BEGIN
IF NOT EXISTS (SELECT 1 FROM Zapisnik WHERE id_na_zapisnik = p_id_zapisnik) THEN
RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
END IF;
IF NOT EXISTS (SELECT 1 FROM Prekrsok WHERE id_prekrsok = p_id_prekrsok) THEN
RAISE EXCEPTION 'Prekrsokot so id % ne postoi!', p_id_prekrsok;
END IF;

SELECT status INTO v_status_uplata
FROM Uplata WHERE id_zapisnik = p_id_zapisnik;

IF v_status_uplata = 'Plateno' THEN
    RAISE EXCEPTION 'Zapisnik % e vekje platen, ne moze da se dodavaat novi stavki!', p_id_zapisnik;
END IF;

SELECT COALESCE(MAX(reden_broj), 0) + 1 INTO v_reden_broj
FROM Stavka_Zapisnik WHERE id_na_zapisnik = p_id_zapisnik;

INSERT INTO Stavka_Zapisnik (reden_broj, id_na_zapisnik, id_na_prekrsok)
VALUES (v_reden_broj, p_id_zapisnik, p_id_prekrsok);

RAISE NOTICE 'Dodadena stavka so reden_broj % vo zapisnik %.', v_reden_broj, p_id_zapisnik;
END;
$$;

Повик:

CALL dodadi_stavka_zapisnik(30000005, 3);

Процедура 3: plati_kazna

Ја плаќа казната за даден записник-го менува статусот во 'Plateno', го запишува датумот и начинот на плаќање. Проверува дали уплатата постои, дали веќе е платена, дали има износ за наплата и дали начинот на плаќање е валиден.

CREATE OR REPLACE PROCEDURE plati_kazna(
    p_id_zapisnik    int,
    p_nacin_plakanje varchar(30)
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_status varchar(30);
    v_iznos  numeric(10,2);
BEGIN
 
    SELECT status, iznos INTO v_status, v_iznos
    FROM Uplata
    WHERE id_zapisnik = p_id_zapisnik;
 
    IF v_status IS NULL THEN
        RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    END IF;
 
    IF v_status = 'Plateno' THEN
        RAISE EXCEPTION 'Kaznata za zapisnik % e vekje platena!', p_id_zapisnik;
    END IF;
 
    IF v_iznos <= 0 THEN
        RAISE EXCEPTION 'Zapisnikot % nema stavki/kazni za naplata!', p_id_zapisnik;
    END IF;
 
    IF p_nacin_plakanje NOT IN ('E-bankarstvo', 'Platezna karticka', 'Gotovo/Uplatnica') THEN
        RAISE EXCEPTION 'Nepoznat nacin na plakanje: %. Dozvoleni: E-bankarstvo, Platezna karticka, Gotovo/Uplatnica.', p_nacin_plakanje;
    END IF;
 
 
    UPDATE Uplata
    SET status         = 'Plateno',
        datum_uplata   = CURRENT_DATE,
        nacin_plakanje = p_nacin_plakanje
    WHERE id_zapisnik = p_id_zapisnik;
 
    RAISE NOTICE 'Uspesno naplatena kazna od % za zapisnik % (%).', v_iznos, p_id_zapisnik, p_nacin_plakanje;
END;
$$;

Повик:

CALL plati_kazna(30000005, 'E-bankarstvo');

Процедура 4: dodadi_kategorija_na_vozacka

Кога возач полага дополнителна категорија (на пр. има B, полага C), ја додава на возачката. Проверува дали дозволата постои и не е истечена, дали категоријата постои и не е веќе додадена, и дали датумот на полагање е валиден.

REATE OR REPLACE PROCEDURE dodadi_kategorija_na_vozacka(
    p_broj_dozvola    varchar(30),
    p_id_kategorija   int,
    p_datum_polaganje date DEFAULT CURRENT_DATE
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_datum_vaznost_do date;
    v_datum_izdavanje  date;
    v_kod_kategorija   varchar(3);
BEGIN
 
    SELECT datum_vaznost_do, datum_izdavanje
    INTO v_datum_vaznost_do, v_datum_izdavanje
    FROM Vozacka_dozvola
    WHERE broj_dozvola = p_broj_dozvola;
 
    IF v_datum_vaznost_do IS NULL THEN
        RAISE EXCEPTION 'Vozackata dozvola so broj % ne postoi!', p_broj_dozvola;
    END IF;
 
 
    IF v_datum_vaznost_do < CURRENT_DATE THEN
        RAISE EXCEPTION 'Vozackata dozvola % e istechena (vaznost do %)! Mora prvo da se obnovi.',
            p_broj_dozvola, v_datum_vaznost_do;
    END IF;
 
 
    SELECT kod INTO v_kod_kategorija
    FROM Kategorija
    WHERE id_kategorija = p_id_kategorija;
 
    IF v_kod_kategorija IS NULL THEN
        RAISE EXCEPTION 'Kategorijata so id % ne postoi!', p_id_kategorija;
    END IF;
 
 
    IF EXISTS (
        SELECT 1 FROM Kategorija_Vozacka_dozvola
        WHERE broj_dozvola = p_broj_dozvola
          AND id_kategorija = p_id_kategorija
    ) THEN
        RAISE EXCEPTION 'Kategorijata % vekje e dodadena na vozackata %!',
            v_kod_kategorija, p_broj_dozvola;
    END IF;
 
 
    IF p_datum_polaganje > CURRENT_DATE THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide vo idnina!', p_datum_polaganje;
    END IF;
 
    IF p_datum_polaganje < v_datum_izdavanje THEN
        RAISE EXCEPTION 'Datumot na polaganje % ne moze da bide pred izdavanjeto na vozackata (%).',
            p_datum_polaganje, v_datum_izdavanje;
    END IF;
 
 
    INSERT INTO Kategorija_Vozacka_dozvola (id_kategorija, broj_dozvola, datum_polaganje)
    VALUES (p_id_kategorija, p_broj_dozvola, p_datum_polaganje);
 
    RAISE NOTICE 'Uspesno dodadena kategorija % na vozackata % (polagano na %).',
        v_kod_kategorija, p_broj_dozvola, p_datum_polaganje;
END;
$$;

Повик:

CALL dodadi_kategorija_na_vozacka('L8207294', 7);
CALL dodadi_kategorija_na_vozacka('L8207294', 9, '2025-11-15');

Процедура 5: zatvori_zapisnik_po_uplata

Го затвора записникот (status_zapisnik → 'Zatvoren') откако казната за него е платена. Дозволува затворање само ако постои уплата за записникот и нејзиниот статус е 'Plateno'; не дозволува повторно затворање на веќе затворен записник. Вообичаено се повикува веднаш по plati_kazna.

CREATE OR REPLACE PROCEDURE zatvori_zapisnik_po_uplata(
    p_id_zapisnik int
)
    LANGUAGE plpgsql
AS $$
DECLARE
    v_status_uplata    varchar(30);
    v_status_zapisnik  varchar(20);
BEGIN
 
    SELECT status_zapisnik INTO v_status_zapisnik
    FROM Zapisnik
    WHERE id_na_zapisnik = p_id_zapisnik;
 
    IF v_status_zapisnik IS NULL THEN
        RAISE EXCEPTION 'Zapisnikot so id % ne postoi!', p_id_zapisnik;
    END IF;
 
    IF v_status_zapisnik = 'Zatvoren' THEN
        RAISE EXCEPTION 'Zapisnikot % e vekje zatvoren!', p_id_zapisnik;
    END IF;
 
 
    SELECT status INTO v_status_uplata
    FROM Uplata
    WHERE id_zapisnik = p_id_zapisnik;
 
    IF v_status_uplata IS NULL THEN
        RAISE EXCEPTION 'Ne postoi uplata za zapisnik %!', p_id_zapisnik;
    END IF;
 
    IF v_status_uplata <> 'Plateno' THEN
        RAISE EXCEPTION 'Zapisnikot % ne moze da se zatvori bidejki uplatata e so status "%" (mora da bide Plateno)!',
            p_id_zapisnik, v_status_uplata;
    END IF;
 
 
    UPDATE Zapisnik
    SET status_zapisnik = 'Zatvoren'
    WHERE id_na_zapisnik = p_id_zapisnik;
 
    RAISE NOTICE 'Zapisnik % e zatvoren po izvrshena uplata.', p_id_zapisnik;
END;
$$;

Повик:

CALL plati_kazna(30000005, 'E-bankarstvo');
CALL zatvori_zapisnik_po_uplata(30000005);

3. Дополнителна функција

Функција што ги обработува задоцнетите неплатени казни: на неплатените постари од 8 дена им го зголемува износот за 50% (само ако сè уште не се зголемени), а на тие постари од 2 месеци им го менува статусот во 'Sudska_postapka'. Враќа порака дека постапката е успешно извршена.

Kолоната kazna_zgolemenaни е од корист кога ја зголемуваме казната затоа што со неа знаеме дали одредената казна е зголемена веќе и дали треба нејзино зголемување.

CREATE OR REPLACE FUNCTION azuriraj_kazna_plakanja()
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE Uplata u
SET iznos = u.iznos + (u.iznos * 0.50),
kazna_zgolemena = true
FROM Zapisnik z
WHERE u.id_zapisnik = z.id_na_zapisnik
AND u.status = 'Neplateno'
AND u.kazna_zgolemena = false
AND z.datum < CURRENT_DATE - INTERVAL '8 days';

UPDATE Uplata u
SET status = 'Sudska_postapka'
FROM Zapisnik z
WHERE u.id_zapisnik = z.id_na_zapisnik
  AND u.status = 'Neplateno'
  AND z.datum < CURRENT_DATE - INTERVAL '2 months';

RETURN 'Постапката за автоматско ажурирање на неплатените казни е успешно извршена.';
END;
$$;

Повик:

SELECT azuriraj_kazna_plakanja();
SELECT status, COUNT(*) FROM Uplata GROUP BY status;

Оваа функција е наменета да се извршува автоматски како закажана задача што периодично ги обработува задоцнетите неплатени казни-ги зголемува застарените и најстарите ги префрла во судска постапка без рачна интервенција.