DELIMITER $$
CREATE TRIGGER tr_arrived_flights
AFTER UPDATE
ON flights
FOR EACH ROW
BEGIN
	IF (old.status<> 'Arrived' and old.status<>'Cancelled' AND new.status = 'Arrived')
    THEN
    INSERT INTO arrived_flights (flight_id, arrival_time, origin, destination, passengers)
    VALUES (	old.flight_id,
				new.arrival_time,

				(SELECT a.airport_name
				FROM flights AS f
				INNER JOIN airports AS a
				ON f.origin_airport_id = a.airport_id
				AND f.flight_id = old.flight_id),

				(SELECT a.airport_name
				FROM flights AS f
				INNER JOIN airports AS a
				ON f.destination_airport_id = a.airport_id
				AND f.flight_id = old.flight_id),

                (SELECT count(c.customer_id)
				FROM flights AS f
				INNER JOIN tickets AS t
				ON t.flight_id=f.flight_id
				AND f.flight_id = old.flight_id
				INNER JOIN customers AS c
				ON t.customer_id=c.customer_id)
    );
    END IF;

END $$

DELIMITER ;