Changes between Version 8 and Version 9 of AnalyticalStatisticalQuerying

Timestamp:

09/27/25 14:31:19 (2 weeks ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -265 +265 @@
 
 
-2. Trigger to apply discount to student and child tickets
+== 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. 
@@ -283 +283 @@
 $$ LANGUAGE plpgsql; 
 CREATE TRIGGER apply_student_ticket_discount 
-        AFTER INSERT ON student_ticket 
+        BEFORE INSERT ON student_ticket 
         FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount(); 
 
 CREATE TRIGGER apply_child_ticket_discount 
-        AFTER INSERT ON child_ticket F
+        BEFORE 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:
+''Example: If we select the existing child tickets and their appropriate base prices (i.e original prices) using a simple query:''
 
 
@@ -306 +305 @@
 }}}
 
-We will get the following results:
-
-
-
-
+''We will get the following results:''
+[[Image(child_ticket_prices.png)]]
+
+
+== 3. Update total payment ==
+
+This trigger calculates the total payment for an account and should work together with the previous trigger for situations where we buy multiple tickets, including special types of tickets (child and student tickets). It is executed **AFTER** applying the discounts.
+
+{{{
+-- UPDATE TOTAL PAYMENT TOTAL PRICE (TRIGGER AFTER CHILD/STUDENT DISCOUNT IS APPLIED)
+
+CREATE OR REPLACE FUNCTION update_payment_total()
+RETURNS TRIGGER AS $$ 
+BEGIN UPDATE payment 
+        SET total_price = ( SELECT COALESCE(SUM(price), 0) 
+                                                FROM ticket 
+                                                WHERE payment_id = NEW.payment_id) 
+        WHERE payment_id = NEW.payment_id; 
+
+RETURN NEW; END; $$ 
+LANGUAGE plpgsql; 
+
+CREATE TRIGGER trg_update_payment_total_insert 
+AFTER INSERT ON ticket 
+FOR EACH ROW EXECUTE FUNCTION update_payment_total(); 
+
+CREATE TRIGGER trg_update_payment_total_update 
+AFTER UPDATE OF price, payment_id ON ticket 
+FOR EACH ROW EXECUTE FUNCTION update_payment_total();
+
+CREATE TRIGGER trg_update_payment_total_delete 
+AFTER DELETE ON ticket 
+FOR EACH ROW EXECUTE FUNCTION update_payment_total();
+}}}
+
+
+