Changes between Initial Version and Version 1 of Trigger1

Timestamp:

08/21/26 07:32:07 (28 hours ago)

Author:

235018

Comment:

--

Trigger1

@@ +1 @@
+= Automatic calculation of store rating from order reviews
+
+=== Description
+This trigger automatically calculates and updates the rating of a store whenever a customer review is added, modified, or deleted.
+
+The store rating is calculated as the average of all ratings given to orders associated with that store.
+
+This ensures that the rating attribute in the STORE table always reflects the current customer reviews without requiring the application to manually recalculate it.
+
+==== Tables involved
+- Store
+- Order
+- Review
+- for_store
+
+==== Type of trigger
+- AFTER INSERT
+- AFTER UPDATE
+- AFTER DELETE
+Activated on the review table.
+
+==== SQL Code
+{{{#!sql
+CREATE OR REPLACE FUNCTION update_store_rating()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_order_num INT;
+    v_store_id INT;
+BEGIN
+    -- Determine which order was affected
+    IF TG_OP = 'DELETE' THEN
+        v_order_num := OLD.order_num;
+    ELSE
+        v_order_num := NEW.order_num;
+    END IF;
+
+    -- Find the store associated with the order
+    SELECT fs.store_id
+    INTO v_store_id
+    FROM for_store fs
+    JOIN "order" o
+        ON o.order_num = v_order_num
+    WHERE fs.request_num IS NOT NULL
+    LIMIT 1;
+
+    -- Update the store rating
+    IF v_store_id IS NOT NULL THEN
+        UPDATE store s
+        SET rating = (
+            SELECT COALESCE(AVG(r.rating), 0)
+            FROM review r
+            JOIN "order" o
+                ON o.order_num = r.order_num
+            -- The exact relationship between orders and stores
+            -- should be used here according to the final schema
+            WHERE o.order_num IN (
+                SELECT i.order_num
+                FROM includes i
+                JOIN sells sl
+                    ON sl.code = i.code
+                WHERE sl.store_id = v_store_id
+            )
+        )
+        WHERE s.store_id = v_store_id;
+    END IF;
+
+    RETURN NULL;
+END;
+$$;
+
+
+CREATE TRIGGER trg_update_store_rating
+AFTER INSERT OR UPDATE OR DELETE
+ON review
+FOR EACH ROW
+EXECUTE FUNCTION update_store_rating();
+
+}}}
+
+=== Logic explanation
+
+Whenever a review is inserted, updated, or deleted, the trigger:
+
+**1.** Identifies the affected order_num.
+**2.** Determines which store is associated with the order.
+**3.** Finds all reviews belonging to orders associated with that store.
+**4.** Calculates the average review rating using AVG().
+**5.** Updates the rating attribute of the corresponding STORE.
+
+COALESCE is used so that a store with no reviews receives a rating of 0 instead of NULL.
+
+=== Reason for trigger
+
+This trigger is useful because:
+
+- Rating is a derived value.
+- The application should not have to manually recalculate store ratings.
+- Ratings remain consistent after reviews are inserted, modified, or removed.
+- It prevents outdated ratings from remaining in the STORE table.