Consider the following two relations:

create table ffMiles(
  passengerName VARCHAR2(20),
  flightDate    DATE,
  flightNum     VARCHAR2(20), -- use 'BONUS' for bonus miles; 'CREDIT' for use of miles
  miles         NUMBER(10),
  AwardOrUse    CHAR(1) CHECK(AwardOrUse in ('a','u')),
  primary key (passengerName,flightDate,flightNum)
);

create table passengerCharge (
  passengerName VARCHAR2(20),
  flightDate    DATE,
  flightNum     VARCHAR2(20),
  milesPurchased NUMBER(10),
  dollarCharge   NUMBER(10,2),
  primary key (passengerName,flightDate,flightNum)
);

ffMiles records the number of miles a particular passenger is awarded or
uses on a particular flight.

passengerCharge records passenger charges as described later in second trigger example.

Example 1:

Write a trigger that rewards double miles for a flight that a passenger
takes once she has already reached 100,000 miles.

create or replace trigger doubleMiles
before insert on ffMiles
for each row
when (new.awardOrUse = 'a')
declare 
  totAwarded integer;
  totUsed integer;
begin
  if ((:new.flightNum != 'BONUS') and
      (:new.flightNum != 'CREDIT')) then
    select sum(miles) into   totAwarded
    from   ffMiles
    where passengerName=:new.passengerName and awardOrUse = 'a';
    select sum(miles) into   totUsed
    from   ffMiles
    where passengerName=:new.passengerName and awardOrUse = 'u';
    if (totUsed is null) then
      totUsed := 0;
    end if;
    --dbms_output.put_line('Total Awarded = ' || totAwarded);
    --dbms_output.put_line('Total Used = ' || totUsed);
    if (totAwarded-totUsed) >= 100000 then
        --dbms_output.put_line('Total Available = ' || (totAwarded - totUsed));
        insert into ffMiles values
          (:new.passengerName,:new.flightDate,'BONUS',:new.miles,'a');
      end if;
  end if;
end;

Example 2:

Write a trigger that is activated when a passenger tries to use
miles for a free flight. If the passenger does not have enough miles
to use, they may pay for upto 20% of the miles required. For example,
if a passenger has 60,000 miles in their account she can use upto 72,000
miles and pay for the extra 12,000 miles or however many miles (less that
12,000) she needs.

The payment is recorded as an entry in the passengerCharge table.
The trigger should charge the user $25 for each 1000 miles
the passenger needs beyond the miles they have.
Of course, if the passenger needs more than 20% to use, they should not
be allowed to use the miles. However, this check for over 20% is
not the responsibility of the trigger. We will assume that the
application program makes sure that there is enough miles before an
insert statement is issued.

create or replace trigger payMiles
before insert on ffMiles
for each row
when (new.awardOrUse = 'u')
declare 
  totAwarded integer;
  totUsed integer;
  totAvailable integer;
  extraMiles integer;
begin
  select sum(miles) into   totAwarded
  from   ffMiles
  where passengerName=:new.passengerName and awardOrUse = 'a';
  select sum(miles) into   totUsed
  from   ffMiles
  where passengerName=:new.passengerName and awardOrUse = 'u';
  if (totUsed is null) then
    totUsed := 0;
  end if;
  totAvailable := totAwarded - totUsed;
  --dbms_output.put_line('Total Awarded = ' || totAwarded);
  --dbms_output.put_line('Total Used = ' || totUsed);
  if (:new.miles <= 1.2*totAvailable) and (:new.miles > totAvailable)  then
    extraMiles := :new.miles - totAvailable;
    --dbms_output.put_line('Total Available = ' || totAvailable);
    insert into passengerCharge values
        (:new.passengerName,:new.flightDate,:new.flightNum,
         extraMiles,0.025*extraMiles);
    insert into ffMiles values
        (:new.passengerName,:new.flightDate,'CREDIT',extraMiles,'a');
  end if;
end;