Changes between Version 11 and Version 12 of AnalyticalStatisticalQuerying

Timestamp:

09/28/25 17:14:15 (2 weeks ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -207 +207 @@
 
 }}}
-
-
-----
-
-
-= Triggers =
-
-== 1. Update free seats based on ticket purchase ==
-
-This trigger calculates the number of available tickets after purchasing a ticket and can be tested via the application, by trying to buy new tickets.
-
-{{{
--- TRIGGER TO UPDATE FREE SEATS
-CREATE OR REPLACE FUNCTION update_free_seats()
-RETURNS TRIGGER AS $$
-BEGIN
-    IF TG_OP = 'INSERT' THEN
-        UPDATE trip 
-                SET free_seats = free_seats - 1 
-                WHERE trip_id = NEW.trip_id;
-        RETURN NEW;
-    ELSIF TG_OP = 'DELETE' THEN
-        UPDATE trip 
-                SET free_seats = free_seats + 1 
-                WHERE trip_id = OLD.trip_id;
-        RETURN OLD;
-    END IF;
-    RETURN NULL;
-END;    
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER ticket_insert_update_seats
-    AFTER INSERT ON ticket
-    FOR EACH ROW
-    EXECUTE FUNCTION update_free_seats();
-
-CREATE TRIGGER ticket_delete_update_seats
-    AFTER DELETE ON ticket
-    FOR EACH ROW
-    EXECUTE FUNCTION update_free_seats();
-}}}
-
-
-Here's a short example:
-
-[[Image(trip_before.png)]]
-
-If we purchase 3 tickets:
-
-[[Image(purchase.png)]]
-
-
-We get the new seat availability:
-
-[[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 
-        BEFORE INSERT ON student_ticket 
-        FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount(); 
-
-CREATE TRIGGER apply_child_ticket_discount 
-        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:''
-
-
-{{{
-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:''
-
-[[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();
-}}}
-
-
-