Changes between Version 1 and Version 2 of Triggers

Timestamp:

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

Author:

221007

Comment:

--

Triggers

@@ -95 +95 @@
 }}}
 
-=== 4. 
+=== 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();
+}}}