Changes between Version 6 and Version 7 of AdvancedDatabaseDevelopment

Timestamp:

06/05/26 15:32:31 (6 days ago)

Author:

232012

Comment:

--

AdvancedDatabaseDevelopment

@@ -31 +31 @@
 * **Description:** This keeps customers from using multiple promotions in a single order. If someone tries to buy a product that is already on sale, this trigger stops them from using their store loyalty points on that same order. It forces the system to only allow one discount type at a time so the store doesn't lose money.
 }}}
+* **Implementation:**
+
+{{{#!div style="text-align: justify; width: 100%;"
+  First, we implement a trigger function that checks if an order combines loyalty points with a discounted product and blocks it if it does. 
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION project.block_double_promo()
+RETURNS TRIGGER AS $$
+DECLARE
+    used_points INTEGER;
+    discounted_product_exists BOOLEAN;
+BEGIN
+    SELECT o.points_used
+    INTO used_points
+    FROM project.orders o
+    WHERE o.order_id = NEW.order_id;
+
+    SELECT EXISTS (
+        SELECT 1
+        FROM project.modification_products mp
+        JOIN project.modifications m
+            ON m.modification_id = mp.modification_id
+        WHERE mp.product_id = NEW.product_id
+          AND m.type_of_modification = 'DISCOUNT'
+          AND m.discount IS NOT NULL
+    )
+    INTO discounted_product_exists;
+
+    IF used_points > 0 AND discounted_product_exists THEN
+        RAISE EXCEPTION
+            'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+{{{#!div style="text-align: justify; width: 100%;"
+  We also create a trigger that runs when a product is added to an order.
+}}}
+
+{{{
+DROP TRIGGER IF EXISTS trg_block_double_promo_order_products
+ON project.order_products;
+
+CREATE TRIGGER trg_block_double_promo_order_products
+BEFORE INSERT OR UPDATE ON project.order_products
+FOR EACH ROW
+EXECUTE FUNCTION project.block_double_promo();
+}}}
+
+{{{#!div style="text-align: justify; width: 100%;"
+  Then we add another trigger function that checks if points are being added to an order that already contains discounted products.
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION project.block_points_on_discounted_order()
+RETURNS TRIGGER AS $$
+DECLARE
+    discounted_product_exists BOOLEAN;
+BEGIN
+    SELECT EXISTS (
+        SELECT 1
+        FROM project.order_products op
+        JOIN project.modification_products mp
+            ON mp.product_id = op.product_id
+        JOIN project.modifications m
+            ON m.modification_id = mp.modification_id
+        WHERE op.order_id = NEW.order_id
+          AND m.type_of_modification = 'DISCOUNT'
+          AND m.discount IS NOT NULL
+    )
+    INTO discounted_product_exists;
+
+    IF NEW.points_used > 0 AND discounted_product_exists THEN
+        RAISE EXCEPTION
+            'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+{{{#!div style="text-align: justify; width: 100%;"
+  Finally, we implement a trigger that runs when loyalty points are added or changed on an order.
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION project.block_points_on_discounted_order()
+RETURNS TRIGGER AS $$
+DECLARE
+    discounted_product_exists BOOLEAN;
+BEGIN
+    SELECT EXISTS (
+        SELECT 1
+        FROM project.order_products op
+        JOIN project.modification_products mp
+            ON mp.product_id = op.product_id
+        JOIN project.modifications m
+            ON m.modification_id = mp.modification_id
+        WHERE op.order_id = NEW.order_id
+          AND m.type_of_modification = 'DISCOUNT'
+          AND m.discount IS NOT NULL
+    )
+    INTO discounted_product_exists;
+
+    IF NEW.points_used > 0 AND discounted_product_exists THEN
+        RAISE EXCEPTION
+            'Double promotion is not allowed: loyalty points cannot be used on an order containing discounted products.';
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+==== Price Drop Alert
+
+{{{#!div style="text-align: justify; width: 100%;"
+* **Description:** When a product manager drops the price of a product, this trigger would notify users that have that same product in their wishlists. It scans the wishlists of all users to see who has been eyeing that specific item. For every enthusiast found, it automatically writes a notification record into the system tables so the application knows exactly who to alert or email about the price cut.
+}}}
 * **Implementation:**  
 
-==== Price Drop Alert
-
-{{{#!div style="text-align: justify; width: 100%;"
-* **Description:** When a product manager drops the price of a product, this trigger would notify users that have that same product in their wishlists. It scans the wishlists of all users to see who has been eyeing that specific item. For every enthusiast found, it automatically writes a notification record into the system tables so the application knows exactly who to alert or email about the price cut.
-}}}
-* **Implementation:**  
-
+{{{#!div style="text-align: justify; width: 100%;"
   First, we need to create a table that will be used to store information about users who should be notified when a product's price decreases.
+}}}
 
 {{{
@@ -59 +178 @@
 }}}
 
+{{{#!div style="text-align: justify; width: 100%;"
   Then, we need to create a trigger function that defines the logic that automatically identifies interested users and creates notification records whenever a product becomes cheaper.
+}}}
 
 {{{
@@ -95 +216 @@
 }}}
 
+{{{#!div style="text-align: justify; width: 100%;"
   Finally, the trigger attaches the notification logic to the products table so it executes automatically after a price reduction occurs.
+}}}
 
 {{{