Фаза 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 'Полицаецот со ЕМБГ % не постои во системот!', NEW.EMBG_P;
END IF;

IF NEW.datum_od IS NULL THEN
    RAISE EXCEPTION 'Почетокот на работниот однос (datum_od) мора да биде дефиниран!';
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 'Полицаецот со ЕМБГ % веќе има активен работен ангажман во друга полициска станица!', 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. Тој прави соодветна валидација за постоење на сопственикот (граѓанинот) и самото возило преку нивните примарни клучеви. Како клучен дел од валидацијата, тригерот користи регуларен израз (REGEX) за да осигура дека регистарската табличка го следи официјалниот македонски стандард (на пр. SK-1234-AA или BT-888-ZZ).

CREATE OR REPLACE FUNCTION proverka_registracija_integritet()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM Gragjanin WHERE EMBG = NEW.EMBG) THEN
RAISE EXCEPTION 'Граѓанинот со ЕМБГ % не постои во системот!', NEW.EMBG;
END IF;

IF NOT EXISTS (SELECT 1 FROM Vozilo WHERE broj_na_sasija = NEW.broj_na_sasija) THEN
    RAISE EXCEPTION 'Возилото со број на шасија % не постои во системот!', NEW.broj_na_sasija;
END IF;

IF NEW.registerska_tablica IS NOT NULL AND NOT (UPPER(NEW.registerska_tablica) ~ '^[A-Z]{2}-[0-9]{3,4}-[A-Z]{2}$') THEN
    RAISE EXCEPTION 'Регистарската табличка "%" не е во валиден формат! Очекуван формат: ZZ-0000-ZZ (напр. SK-1234-AA)', NEW.registerska_tablica;
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'. Износот го пополнува тригерот штом ќе се вметне првата ставка. Прво проверува дали постојат граѓанинот, полицаецот, возилото, прекршокот и случајот.

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: %.', 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), ја додава на возачката. Проверува дали дозволата постои и не е истечена, дали категоријата постои и не е веќе додадена, и дали датумот на полагање е валиден.

CREATE 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 %)!', 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 %.', v_kod_kategorija, p_broj_dozvola;
END;
$$;

Повик:

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

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

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

Прво ја прошируваме табелата Uplata со новиот статус и со колона за обележување дали казната е веќе зголемена:

ALTER TABLE Uplata DROP CONSTRAINT IF EXISTS uplata_status_check;
ALTER TABLE Uplata ADD CONSTRAINT uplata_status_check
CHECK (status IN ('Plateno', 'Neplateno', 'Sudska_postapka'));

ALTER TABLE Uplata ADD COLUMN IF NOT EXISTS kazna_zgolemena boolean DEFAULT false;

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;

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