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