Guest User

18. Update trigger

a guest
Oct 17th, 2017
83
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TRIGGER t_updated_arrivals
  2. BEFORE UPDATE ON flights
  3. FOR EACH ROW
  4. BEGIN
  5.     DECLARE passengers INT;
  6.     DECLARE origin VARCHAR(50);
  7.     DECLARE destination VARCHAR(50);
  8.     SET passengers := (SELECT COUNT(t.ticket_id) FROM tickets AS t
  9.     INNER JOIN flights AS f ON t.flight_id = new.flight_id AND f.flight_id = new.flight_id);
  10.    
  11.     SET origin := (SELECT airport_name FROM airports AS a
  12.         where new.origin_airport_id = a.airport_id);
  13.  
  14.     SET destination := (SELECT airport_name FROM airports AS a
  15.         where new.destination_airport_id = a.airport_id);
  16.  
  17.     IF(old.`status` = 'Departing' OR old.`status` = 'Delayed') THEN
  18.         INSERT INTO arrived_flights(flight_id, arrival_time,origin, destination, passengers) VALUES (new.flight_id, new.arrival_time, origin, destination, passengers);
  19.     END IF;
  20. END
RAW Paste Data