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();