Changes between Initial Version and Version 1 of Triggers

Timestamp:

08/24/25 17:06:20 (2 months ago)

Author:

221007

Comment:

--

Triggers

@@ +1 @@
+== Употреба на тригери во апликацијата
+
+Во овој дел ќе прикажам каде и како ги комбинирав тригерите со функции, кои ми донесоа многу пофлексибилна нивна употреба и попрофесионален дизајн на базата.
+
+=== 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.