Changes between Version 2 and Version 3 of Triggers

Timestamp:

08/24/25 17:24:34 (2 months ago)

Author:

221007

Comment:

--

Triggers

@@ -212 +212 @@
 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 форматот
+
+{{{
+--function for validating embg format
+CREATE OR REPLACE FUNCTION validate_embg_format()
+RETURNS TRIGGER AS $$
+DECLARE
+    date_part TEXT;
+    gender_code TEXT;
+BEGIN
+    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;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER check_embg_format
+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();
+}}}
+