Changes between Version 29 and Version 30 of AdvancedApplicationDevelopment

Timestamp:

09/30/25 10:21:53 (4 weeks ago)

Author:

223270

Comment:

--

AdvancedApplicationDevelopment

@@ -117 +117 @@
 END;
 $$;
+}}}
+
+3. Наместо автоматско деактивирање, тригерот само бележи дали е под прагот за просечна оцена >=3 со минимум 5 рецензии.
+{{{
+ALTER TABLE destination
+ADD COLUMN IF NOT EXISTS flagged_low_rating BOOLEAN NOT NULL DEFAULT FALSE;
+
+CREATE OR REPLACE FUNCTION recalc_destination_flag(p_dest INT)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE
+    avg_rating FLOAT;
+    total_reviews INT;
+BEGIN
+    SELECT COUNT(*), COALESCE(AVG(quality),0) INTO total_reviews, avg_rating
+    FROM review
+    WHERE id_destination = p_dest;
+
+    IF total_reviews >= 5 AND avg_rating < 3 THEN
+        UPDATE destination SET flagged_low_rating = TRUE WHERE id_destination = p_dest;
+    ELSE
+        UPDATE destination SET flagged_low_rating = FALSE WHERE id_destination = p_dest;
+    END IF;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION trg_review_flag_trigger()
+RETURNS TRIGGER LANGUAGE plpgsql AS $$
+BEGIN
+    IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
+        PERFORM recalc_destination_flag(NEW.id_destination);
+    ELSIF TG_OP = 'DELETE' THEN
+        PERFORM recalc_destination_flag(OLD.id_destination);
+    END IF;
+    RETURN COALESCE(NEW, OLD);
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trg_review_after ON review;
+CREATE TRIGGER trg_review_after
+AFTER INSERT OR UPDATE OR DELETE ON review
+FOR EACH ROW EXECUTE FUNCTION trg_review_flag_trigger();
 }}}