Changes between Version 19 and Version 20 of UseCaseModel/evaluation

Timestamp:

09/01/25 23:41:49 (3 days ago)

Author:

175012

Comment:

--

UseCaseModel/evaluation

@@ -135 +135 @@
 == Примери за тригери 
 
-1. Проверка на датумска логика
-{{{#!sql
-
-CREATE OR REPLACE FUNCTION project.validate_policy_dates()
+1. Лог за бришење на клиенти (Customer)
+{{{#!sql
+
+-- Табела за логирање
+CREATE TABLE project_2425.Customer_log (
+  log_id BIGSERIAL PRIMARY KEY,
+  c_id BIGINT,
+  name VARCHAR(100),
+  email VARCHAR(255),
+  deleted_at TIMESTAMP DEFAULT NOW()
+);
+
+CREATE OR REPLACE FUNCTION log_customer_delete()
 RETURNS TRIGGER AS $$
 BEGIN
-    IF NEW.e_date <= NEW.s_date THEN
-        RAISE EXCEPTION 'End date must be after start date. Start: %, End: %', NEW.s_date, NEW.e_date;
-    END IF;
-    RETURN NEW;
+  INSERT INTO project_2425.Customer_log(c_id, name, email)
+  VALUES (OLD.c_id, OLD.name, OLD.email);
+  RETURN OLD;
 END;
 $$ LANGUAGE plpgsql;
 
-CREATE TRIGGER trigger_validate_policy_dates
-BEFORE INSERT OR UPDATE ON project.policy
-FOR EACH ROW EXECUTE FUNCTION project.validate_policy_dates();
+CREATE TRIGGER trg_log_customer_delete
+AFTER DELETE ON project_2425.Customer
+FOR EACH ROW
+EXECUTE FUNCTION log_customer_delete();
 
 }}}
@@ -190 +199 @@
 }}}
 
-3. Формат на табели за колата
-
-{{{#!sql
-        CREATE OR REPLACE FUNCTION validate_license_plate() 
-        RETURNS TRIGGER AS $$
-        BEGIN
-            -- Check if the license plate matches the expected format (XX-1234-XX)
-            IF NEW.license_plate !~ '^[A-Z]{2}-\d{4}-[A-Z]{2}$' THEN
-                RAISE EXCEPTION 'Invalid license plate format: %', NEW.license_plate;
-            END IF;
-            RETURN NEW;
-        END;
-        $$ LANGUAGE plpgsql;
-
-
-CREATE TRIGGER trigger_validate_license_plate
-BEFORE INSERT OR UPDATE ON vehicles
+3. Проверка дали возило е веќе осигурано (Auto_pol)
+
+{{{#!sql
+        CREATE OR REPLACE FUNCTION check_vehicle_policy()
+RETURNS TRIGGER AS $$
+DECLARE
+  active_count INT;
+BEGIN
+  SELECT COUNT(*) INTO active_count
+  FROM project_2425.Auto_pol ap
+  JOIN project_2425.Policy pol ON pol.p_id = ap.pol_id
+  WHERE ap.v_id = NEW.v_id
+    AND pol.s_date <= CURRENT_DATE
+    AND pol.e_date >= CURRENT_DATE;
+
+  IF active_count > 0 THEN
+    RAISE EXCEPTION 'Возилото веќе има активна полиса';
+  END IF;
+
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_vehicle_policy
+BEFORE INSERT ON project_2425.Auto_pol
 FOR EACH ROW
-EXECUTE FUNCTION validate_license_plate_format();
+EXECUTE FUNCTION check_vehicle_policy();
 }}}