Changes between Version 31 and Version 32 of konceptualenDizajnLogickiFizicki

Timestamp:

06/26/25 00:03:14 (4 months ago)

Author:

213209

Comment:

--

konceptualenDizajnLogickiFizicki

@@ -31 +31 @@
 
 == Тригери
-[[Image(Validate preorder price trigger.txt)]]
+{{{
+CREATE OR REPLACE FUNCTION trg_validate_preorder_price()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_menu_price NUMERIC;
+    v_restaurant_id INTEGER;
+BEGIN
+    SELECT restaurantid INTO v_restaurant_id
+    FROM reservations
+    WHERE reservationid = NEW.reservation_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Reservation ID % not found', NEW.reservation_id;
+    END IF;
+
+    SELECT price INTO v_menu_price
+    FROM menus
+    WHERE item_name = NEW.preordered_item_name AND restaurant_id = v_restaurant_id;
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Menu item "%" not found for restaurant ID %', NEW.preordered_item_name, v_restaurant_id;
+    END IF;
+
+    IF NEW.quantity <= 0 THEN
+        RAISE EXCEPTION 'Invalid quantity for item "%": must be greater than 0', NEW.preordered_item_name;
+    END IF;
+
+    IF NEW.price_at_order <> v_menu_price THEN
+        RAISE EXCEPTION 'Price mismatch for item "%": expected %, got %',
+            NEW.preordered_item_name, v_menu_price, NEW.price_at_order;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER validate_preorder_price_trigger
+BEFORE INSERT OR UPDATE ON reservation_preordered_items
+FOR EACH ROW
+EXECUTE FUNCTION trg_validate_preorder_price();
+
+--Test cases
+
+INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
+VALUES (22, 'California Roll', 1, 5.99);
+
+INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
+VALUES (14, 'California Roll', 1, 5.99);
+
+INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
+VALUES (14, 'Roll', 1, 8.99);
+
+INSERT INTO reservation_preordered_items (reservation_id, preordered_item_name, quantity, price_at_order)
+VALUES (14, 'California Roll', 0, 8.99);
+}}}
 
 == Погледи(Views)