DatabaseProgramming: AwardMilePoints.sql

create or replace function AwardMilePoints()
returns trigger as $$
declare
    ArrivedStatusID  int;
    BoardedStatusID  int;
begin
    select id
    into strict ArrivedStatusID
    from flightstatus
    where name = 'Arrived';

    select id
    into strict BoardedStatusID
    from ticketstatus
    where name = 'Boarded';

    if new.statusid = ArrivedStatusID
       and old.statusid is distinct from ArrivedStatusID then
        update passenger p
        set milepoints = milepoints + (
            sf.distance *
            case sc.name
                when 'First'    then 3
                when 'Business' then 2
                else 1
            end
        )
        from ticket t
        join scheduledflight sf on sf.id = new.scheduleid
        join seatclass sc on sc.id = t.seatclassid
        where t.flightid = new.id
          and t.ticketstatusid = BoardedStatusID
          and p.id = t.passengerid;
    end if;

    return new;
end;
$$ language plpgsql;

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