Употреба на тригери во апликацијата

Во овој дел ќе прикажам каде и како ги комбинирав тригерите со функции, кои ми донесоа многу пофлексибилна нивна употреба и попрофесионален дизајн на базата.

1. Валидирање на дали Report објектот е соодветен за креирање на AcademicReport

CREATE OR REPLACE FUNCTION validate_academic_report()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Report WHERE report_id = NEW.report_id AND report_type = 'Academic')
	THEN
    	RAISE EXCEPTION 'Report with ID % is not of type Academic!', NEW.report_id;
	END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_academic_report_correctness
BEFORE INSERT ON AcademicReport
FOR EACH ROW
EXECUTE FUNCTION validate_academic_report();

2. Валидирање на дали Person објектот е доволно возрасен за дадена институција која е поврзана со неговиот AcademicReport

--function to check if the age is correct so the person can go or went in the right age at the type of school2
CREATE OR REPLACE FUNCTION validate_academic_age_for_institution()
RETURNS TRIGGER AS $$
DECLARE
    institution_type institution_type;
    person_birth_date DATE;
    report_date DATE;
    person_age INT;
    pers_id INT;
BEGIN
    SELECT type INTO institution_type
    FROM Institution
    WHERE institution_id = NEW.institution_id;

    SELECT p.date_of_birth, r.created_at
    INTO person_birth_date, report_date
    FROM Report r
    JOIN Person p ON r.person_id = p.person_id
    WHERE r.report_id = NEW.report_id;

    SELECT r.person_id INTO pers_id
    FROM Report r
    WHERE r.report_id = NEW.report_id;

    person_age := DATE_PART('year', age(report_date, person_birth_date));

    IF institution_type = 'Primary School' AND (person_age < 5 OR person_age > 16) THEN
        RAISE EXCEPTION 'Person with ID % is not within valid age range for Primary School (actual: %)', pers_id, person_age;
    ELSIF institution_type = 'High School' AND (person_age < 14 OR person_age > 19) THEN
        RAISE EXCEPTION 'Person with ID % is not within valid age range for High School (actual: %)', pers_id, person_age;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_academic_report_person_age
BEFORE INSERT ON AcademicReport
FOR EACH ROW
EXECUTE FUNCTION validate_academic_age_for_institution();

3. Тригер кој осигурува дека AcademicReport не може да е креиран за Person објекти кои се назначени како преминати

CREATE OR REPLACE FUNCTION prevent_academic_reports_after_death()
RETURNS TRIGGER AS $$
DECLARE
    death_date DATE;
    is_dead BOOLEAN;
    type_check VARCHAR;
BEGIN
    SELECT date_of_death, is_alive INTO death_date, is_dead FROM Person WHERE person_id = NEW.person_id;
    IF NEW.report_type = 'Academic' THEN
        IF is_dead = false AND death_date IS NOT NULL AND NEW.created_at > death_date THEN
            RAISE EXCEPTION
                'Cannot create academic report for deceased person (death: %, report date: %)',
                death_date, NEW.created_at;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_prevent_academic_report_after_death
BEFORE INSERT ON Report
FOR EACH ROW
EXECUTE FUNCTION prevent_academic_reports_after_death();

4. Валидирање на годината на создавање на објект од типот Institution

CREATE OR REPLACE FUNCTION validate_institution_year()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.year_established > EXTRACT(YEAR FROM CURRENT_DATE) THEN
        RAISE EXCEPTION 'Institution cannot be established in the future.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_year_established
BEFORE INSERT OR UPDATE ON Institution
FOR EACH ROW
EXECUTE FUNCTION validate_institution_year();

5. Форматирчки тригер кој се однесува на објектите од типот Institution

CREATE OR REPLACE FUNCTION format_institution_fields()
RETURNS TRIGGER AS $$
BEGIN
    NEW.name := INITCAP(NEW.name);
    NEW.city := INITCAP(NEW.city);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_format_institution_fields
BEFORE INSERT OR UPDATE ON Institution
FOR EACH ROW
EXECUTE FUNCTION format_institution_fields();

6. Тригер кој поставува custom description на CriminalReport

CREATE OR REPLACE FUNCTION update_criminal_report_description()
RETURNS TRIGGER AS $$
DECLARE
    deadline_date DATE;
    report_date DATE;
    formatted_fine TEXT;
BEGIN
    SELECT created_at INTO report_date
    FROM report
    WHERE report_id = NEW.report_id;

    IF NEW.punishment_type = 'fine' THEN
        deadline_date := report_date + INTERVAL '1 month';
        formatted_fine := TO_CHAR(NEW.fine_to_pay, 'FM999999990.00');

        UPDATE CriminalReport
        SET descriptive_punishment = FORMAT(
            'The fine to be paid is %s euros, and shall be paid within one month after the report is made (until %s).',
            formatted_fine,
            TO_CHAR(deadline_date, 'YYYY-MM-DD')
        )
        WHERE report_id = NEW.report_id;

    ELSIF NEW.punishment_type = 'prison' THEN
        UPDATE CriminalReport
        SET descriptive_punishment = FORMAT(
            'The accused shall be in prison until %s.',
            TO_CHAR(NEW.release_date, 'YYYY-MM-DD')
        )
        WHERE report_id = NEW.report_id;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_description_on_punishment_insert
AFTER INSERT OR UPDATE ON Punishment
FOR EACH ROW
EXECUTE FUNCTION update_criminal_report_description();

7. Тригер кој поставува вредносна валута за казната на објектот Punishment

CREATE OR REPLACE FUNCTION setting_punishment_depending_on_type()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
    IF NEW.punishment_type = 'fine' THEN
        NEW.value_unit := 'euros';
        IF NEW.fine_to_pay IS NULL THEN
            RAISE EXCEPTION 'Fine punishment must include fine_to_pay amount.';
        END IF;
        IF NEW.release_date IS NOT NULL THEN
            RAISE EXCEPTION 'Fine punishment must not have a release_date.';
        END IF;
    ELSIF NEW.punishment_type = 'prison' THEN
        NEW.value_unit := 'years';
        IF NEW.release_date IS NULL THEN
            RAISE EXCEPTION 'Prison punishment must include release_date.';
        END IF;
        IF NEW.fine_to_pay IS NOT NULL THEN
            RAISE EXCEPTION 'Prison punishment must not include fine_to_pay.';
        END IF;
    ELSE
        RAISE EXCEPTION 'Invalid punishment_type: %', NEW.punishment_type;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_punishment_unit
BEFORE INSERT OR UPDATE ON Punishment
FOR EACH ROW
EXECUTE FUNCTION setting_punishment_depending_on_type();

8. Проверка за дали Person објектот на кого се однесува EmploymentReport е возрасна личност

CREATE OR REPLACE FUNCTION check_if_person_is_adult()
RETURNS TRIGGER AS $$
DECLARE
    person_date_of_birth DATE;
    person_date_of_death DATE;
    person_is_alive_status BOOLEAN;
    created_at_report_date DATE;
BEGIN
    --I need the data so I can make the checks
    SELECT p.date_of_birth, p.is_alive, p.date_of_death, r.created_at
    INTO person_date_of_birth, person_is_alive_status, person_date_of_death, created_at_report_date
    FROM report r
    JOIN person p ON r.person_id = p.person_id
    WHERE r.report_id = NEW.report_id;
    IF person_is_alive_status IS TRUE THEN
        IF EXTRACT(YEAR FROM age(created_at_report_date, person_date_of_birth)) < 18 THEN
            RAISE EXCEPTION 'Person must be adult to have an employment report!';
        END IF;
    ELSIF person_is_alive_status IS FALSE THEN
        IF EXTRACT(YEAR FROM age(created_at_report_date, person_date_of_birth)) < 18 THEN
            RAISE EXCEPTION 'Person was not adult at the time of report creation!';
        ELSIF created_at_report_date > person_date_of_death THEN
            RAISE EXCEPTION 'The report cannot be created after the person''s death!';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_if_person_adult
BEFORE INSERT OR UPDATE ON EmploymentReport
FOR EACH ROW
EXECUTE FUNCTION check_if_person_is_adult();

9. Тригер кој овозможува креирање на профил за секој регистриран корисник на апликацијата, веднаш по регистрација

CREATE FUNCTION create_profile_automatically_when_user_comes() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO UserProfile (user_id, role_id, username)
    VALUES (NEW.user_id, 2, NEW.name || ' ' || NEW.surname);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_create_profile_for_user
AFTER INSERT ON ReportiumUser
FOR EACH ROW
EXECUTE FUNCTION create_profile_automatically_when_user_comes();

10. Валидатор на embg форматот

create function validate_embg_format() returns trigger
    language plpgsql
as
$$
DECLARE
    date_part TEXT;
    gender_code TEXT;
BEGIN
    IF COALESCE(NEW.is_stub, false) THEN
        RETURN NEW;
    END IF;
    date_part := TO_CHAR(NEW.date_of_birth, 'DD')
	|| TO_CHAR(NEW.date_of_birth, 'MM')
	|| RIGHT(TO_CHAR(EXTRACT(YEAR FROM NEW.date_of_birth)::INT,'9999'),3);
	--this is going to fetch the last 3 digits from the year
	--example: if it is 1990 -> it fetches 990
    IF SUBSTRING(NEW.embg FROM 1 FOR 7) <> date_part THEN
        RAISE EXCEPTION 'EMBG date part does not match the date of birth!';
    END IF;
	--gender check
    gender_code := SUBSTRING(NEW.embg FROM 8 FOR 3);
    IF (NEW.gender = 'Male' AND gender_code <> '450') OR
       (NEW.gender = 'Female' AND gender_code <> '455') THEN
        RAISE EXCEPTION 'EMBG gender code does not match gender!';
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_validate_embg
BEFORE INSERT OR UPDATE ON person
FOR EACH ROW
EXECUTE FUNCTION validate_embg_format();

11. Проверка за is_alive статус кај Person објект

CREATE OR REPLACE FUNCTION alive_status_check()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.date_of_death IS NOT NULL THEN
        NEW.is_alive := FALSE;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER alive_status_check
BEFORE INSERT OR UPDATE ON Person
FOR EACH ROW
EXECUTE FUNCTION alive_status_check();