BONUS Section 5: Update Trigger

CREATE TRIGGER tr_SuccesFullFlight
ON
Flights
AFTER UPDATE
AS
BEGIN
    DECLARE @NewFlightStatus VARCHAR(9) = (SELECT ins.Status FROM inserted AS ins);
    DECLARE @OldFlightStatus VARCHAR(9) = (SELECT del.Status FROM deleted AS del);
    IF(@NewFlightStatus = 'Arrived')
    BEGIN
        DECLARE @FlightID INT = (SELECT FlightID FROM inserted)

        DECLARE @Origin VARCHAR(50) = (SELECT  AirportName
                    FROM Airports  AS a
                    INNER JOIN Flights AS f
                    ON f.OriginAirportID = a.AirportID
                    WHERE f.FlightID = @FlightID)

        DECLARE @Destination VARCHAR(50) = (SELECT AirportName
                    FROM Airports  AS a
                    INNER JOIN Flights AS f
                    ON f.DestinationAirportID = a.AirportID
                    WHERE f.FlightID = @FlightID)

        DECLARE @Passengers INT = (SELECT COUNT(t.TicketID)
                FROM Tickets AS t
                INNER JOIN Flights AS f
                ON f.FlightID = t.FlightID
                WHERE f.FlightID = @FlightID)


        DECLARE @ArrivalTime DATETIME = (SELECT ArrivalTime FROM inserted)

        INSERT ArrivedFlights
        (FlightID, ArrivalTime, Origin, Destination, Passengers)
        VALUES
        (@FlightID, @ArrivalTime, @Origin, @Destination, @Passengers)
    END
END