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

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

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.