Changes between Version 7 and Version 8 of AnalyticalStatisticalQuerying

Timestamp:

09/27/25 14:09:36 (2 weeks ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -263 +263 @@
 
 [[Image(trip_after.png)]]
+
+
+2. Trigger to apply discount to student and child tickets
+
+When purchasing a ticket, if its a student or a child ticket there exist special discounts. Since the application supports only buying regular tickets (covered by the application use cases), we will present how buying tickets of this type will automatically apply the discounts. 
+
+
+{{{
+-- TRIGGER TO CALCULATE TICKET PRICE WITH DISCOUNTS     
+CREATE OR REPLACE FUNCTION apply_ticket_discount() 
+RETURNS TRIGGER AS $$ 
+BEGIN 
+        UPDATE ticket 
+        SET price = tr.base_price - (tr.base_price * NEW.discount / 100) 
+        FROM trip tr 
+        WHERE ticket.ticket_id = NEW.ticket_id AND ticket.trip_id = tr.trip_id; 
+        RETURN NEW; 
+END; 
+$$ LANGUAGE plpgsql; 
+CREATE TRIGGER apply_student_ticket_discount 
+        AFTER INSERT ON student_ticket 
+        FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount(); 
+
+CREATE TRIGGER apply_child_ticket_discount 
+        AFTER INSERT ON child_ticket F
+        OR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
+}}}
+
+''Example''
+If we select the existing child tickets and their appropriate base prices (i.e original prices) using a simple query:
+
+
+{{{
+SELECT 
+    t.ticket_id,
+    t.price AS discounted_price,
+    ct.discount,
+        tr.base_price AS original_price
+FROM ticket t
+JOIN child_ticket ct ON ct.ticket_id = t.ticket_id
+JOIN trip tr ON tr.trip_id = t.trip_id;
+}}}
+
+We will get the following results:
+
+
+
+