DatabaseProgramming: UpdateAirplaneFlightHours.sql

create function UpdateAirplaneFlightHours()
returns trigger as $$
declare
    StatusID int;
begin
    select id
    into StatusID
    from flightstatus
    where name like '%Arrived%';

    if new.statusid = StatusID
    and (old.statusid is distinct from StatusID) then
        update airplane
        set totalflighthours = totalflighthours + extract(epoch from (new.arrival - new.departure)) / 3600::int
        where id = new.airplaneid;
    end if;

    return new;
end;
$$ language plpgsql;

create trigger UpdateAirplaneFlightHours
after update of statusid on flight
for each row execute function UpdateAirplaneFlightHours();