BONUS Section 5: Update Trigger

CREATE TRIGGER tr_SuccesFullFlight
ON
Flights
AFTER UPDATE
AS
BEGIN
    DECLARE @NewFlightStatus VARCHAR(9) = (SELECT DISTINCT ins.Status FROM inserted AS ins);
    IF(@NewFlightStatus = 'Arrived')
    BEGIN
        INSERT ArrivedFlights
        (FlightID, ArrivalTime, Origin, Destination, Passengers)
        SELECT * FROM
        (
        SELECT
                ins.FlightID,
                ins.ArrivalTime,
                [Origin] = (
                    SELECT AirportName
                    FROM Airports AS a
                    INNER JOIN Flights AS f ON f.OriginAirportID = a.AirportID
                    WHERE f.FlightID = ins.FlightID),
                [Destination] = (
                    SELECT AirportName
                    FROM Airports AS a
                    INNER JOIN Flights AS f ON f.DestinationAirportID = a.AirportID
                    WHERE f.FlightID = ins.FlightID),
                [Passengers] = (
                    SELECT COUNT(t.TicketID)
                    FROM Tickets AS t
                    INNER JOIN Flights AS f ON f.FlightID = t.FlightID
                    WHERE f.FlightID = ins.FlightID)
        FROM inserted AS ins
        ) w
    END
END