SHARE
TWEET

18. Update trigger

a guest Oct 17th, 2017 69 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top