Changes between Version 4 and Version 5 of ApplicationDevelopment

Timestamp:

09/29/25 14:42:31 (2 weeks ago)

Author:

221507

Comment:

--

ApplicationDevelopment

@@ -62 +62 @@
 
 
-
-
+== Triggers
+
+=== 1. Trigger to update the number of free seats based on ticket insertion/deletion
+
+This trigger automatically adjusts the number of available seats in a trip whenever a ticket is inserted or deleted. On insert, it decreases the free_seats count by one, and on delete, it increases it by one. This ensures that trip capacity stays accurate without requiring manual updates.
+
+{{{
+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();
+}}}
+
+
+=== 2. Ticket discount and total price triggers
+
+These two triggers work together in a way that the discount triggers first calculates and applies the reduced ticket price before the ticket is officially inserted into the database. Once the discounted ticket is saved, the payment triggers recalculate the total payment by summing all ticket prices linked to the same payment record, ensuring the final total reflects the applied discount.
+
+{{{
+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
+    FOR EACH ROW EXECUTE FUNCTION apply_ticket_discount();
+
+
+-- 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();
+}}}
+
+
+=== 3. Trigger to automatically set trip status
+
+This trigger assigns the default status 'NOT_STARTED' to any new trip that doesn't explicitly define a status. It guarantees consistent initialization of trip state at creation time. This prevents null or undefined statuses in downstream queries and logic.
+
+{{{
+CREATE OR REPLACE FUNCTION set_default_trip_status()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.status IS NULL THEN
+        NEW.status = 'NOT_STARTED';
+END IF;
+RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_set_default_trip_status
+    BEFORE INSERT ON trip
+    FOR EACH ROW EXECUTE FUNCTION set_default_trip_status();
+}}}
+
+
+=== 4. Trigger to update trip status
+
+This function adjusts a trip's status based on available seats and trip date. It sets the trip to 'FULL' when no seats remain or resets it to 'NOT_STARTED' if seats reopen. If the trip date is in the past, it marks the trip as 'COMPLETED' to reflect its final state.
+
+
+{{{
+CREATE OR REPLACE FUNCTION update_trip_status()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.free_seats = 0 THEN
+        NEW.status = 'FULL';
+    ELSIF NEW.free_seats > 0 AND OLD.status = 'FULL' THEN
+        NEW.status = 'NOT_STARTED';
+END IF;
+
+    IF NEW.date < CURRENT_DATE THEN
+        NEW.status = 'COMPLETED';
+END IF;
+
+RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_update_trip_status
+    BEFORE UPDATE OF free_seats, date ON trip
+    FOR EACH ROW
+    EXECUTE FUNCTION update_trip_status();
+}}}
+
+=== 5. Trigger to automatically set the ticket purchase time and date
+
+This trigger ensures that every ticket has a purchase date and time set automatically. If either field is missing at insertion, it fills them with the current date and time. It prevents incomplete timestamps and simplifies data entry.
+
+
+{{{
+CREATE OR REPLACE FUNCTION set_purchase_timestamp()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.date_purchased IS NULL THEN
+        NEW.date_purchased = CURRENT_DATE;
+END IF;
+    IF NEW.time_purchased IS NULL THEN
+        NEW.time_purchased = CURRENT_TIME;
+END IF;
+RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_set_purchase_timestamp
+    BEFORE INSERT ON ticket
+    FOR EACH ROW EXECUTE FUNCTION set_purchase_timestamp();
+}}}
+
+
+
+