Changes between Version 5 and Version 6 of AnalyticalStatisticalQuerying

Timestamp:

09/27/25 13:17:42 (2 weeks ago)

Author:

221507

Comment:

--

AnalyticalStatisticalQuerying

@@ -208 +208 @@
 
 
+----
+
+
+= 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();
+}}}
+
+
+