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

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

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();