Changes between Initial Version and Version 1 of Triggers

Timestamp:

01/29/26 01:54:26 (11 days ago)

Author:

233051

Comment:

--

Triggers

@@ +1 @@
+== Triggers ==
+
+=== Build Price Calculation Trigger (build_total_price) ===
+
+**What it does:** Automatically recalculates the `total_price` of a build whenever components are added, removed, or modified. \\
+**When it triggers:** AFTER `INSERT`, `UPDATE`, or `DELETE` on the `build_component` table. \\
+**Why it is useful:** Ensures the build price is always mathematically correct and consistent with the sum of its parts, removing the need for manual calculation in the application layer. \\
+
+{{{
+CREATE OR REPLACE FUNCTION update_build_total_price()
+RETURNS TRIGGER
+LANGUAGE plpgsql AS $$
+DECLARE
+    target_build_id INT;
+BEGIN
+    IF (TG_OP = 'DELETE') THEN
+        target_build_id := OLD.build_id;
+    ELSE
+        target_build_id := NEW.build_id;
+    END IF;
+
+    UPDATE "build"
+    SET "total_price" = (
+        SELECT COALESCE(SUM(c.price * bc.num_components), 0)
+        FROM "build_component" bc
+        JOIN "components" c ON bc.component_id = c.id
+        WHERE bc.build_id = target_build_id
+    )
+    WHERE id = target_build_id;
+
+    RETURN NULL;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trigger_auto_update_price ON "build_component";
+CREATE TRIGGER trigger_auto_update_price
+AFTER INSERT OR UPDATE OR DELETE ON "build_component"
+FOR EACH ROW
+EXECUTE FUNCTION update_build_total_price();
+}}}
+
+=== Self-Review Prevention Trigger (check_review_validity) ===
+
+**What it does:** Checks if the user trying to write a review is the creator of the build. If they are, it raises an exception. \\
+**When it triggers:** BEFORE `INSERT` on the `review` table. \\
+**Why it is useful:** Enforces data integrity by preventing users from artificially boosting their own build's popularity with fake reviews. \\
+
+{{{
+CREATE OR REPLACE FUNCTION check_review_validity()
+RETURNS TRIGGER
+LANGUAGE plpgsql AS $$
+DECLARE
+    build_owner_id INT;
+BEGIN
+    SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;
+
+    IF NEW.user_id = build_owner_id THEN
+        RAISE EXCEPTION 'Cannot review own builds';
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trigger_check_self_review ON "review";
+CREATE TRIGGER trigger_check_self_review
+BEFORE INSERT ON "review"
+FOR EACH ROW
+EXECUTE FUNCTION check_review_validity();
+}}}
+
+=== Self-Rating Prevention Trigger (check_rating_validity) ===
+**What it does:** Checks if the user trying to rate a build (1-5 stars) is the creator of the build. If so, it blocks the action. \\
+**When it triggers:** BEFORE `INSERT` on the `rating_build` table. \\
+**Why it is useful:** Enforces data integrity by preventing users from artificially boosting their own build's popularity with 5 star ratings. \\
+
+{{{
+CREATE OR REPLACE FUNCTION check_rating_validity()
+RETURNS TRIGGER
+LANGUAGE plpgsql AS $$
+DECLARE
+    build_owner_id INT;
+BEGIN
+    SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;
+
+    IF NEW.user_id = build_owner_id THEN
+        RAISE EXCEPTION 'Cannot rate own builds.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$;
+
+DROP TRIGGER IF EXISTS trigger_check_self_rating ON "rating_build";
+CREATE TRIGGER trigger_check_self_rating
+BEFORE INSERT ON "rating_build"
+FOR EACH ROW
+EXECUTE FUNCTION check_rating_validity();
+}}}
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+