create function order_items () returns opaque as '
declare
  oldhand inventory.qty_onhand%TYPE;
  newhand inventory.qty_onhand%TYPE;
  oldorder inventory.qty_onorder%TYPE;
  neworder inventory.qty_onorder%TYPE;
  oldcode inventory.code%TYPE;
  newcode inventory.code%TYPE;
  ooid int4;
  noid int4;
begin
  if TG_OP = ''UPDATE'' or TG_OP = ''DELETE'' then
    oldcode := OLD.inven_code;
  else
    oldcode := NEW.inven_code;
  end if;

  if TG_OP = ''UPDATE'' then
    newcode := NEW.inven_code;
  else
    newcode := oldcode;
  end if;

  select oid, qty_onorder, qty_onhand
   into ooid, oldorder, oldhand
   from inventory
   where code = oldcode;

  if newcode = oldcode then
    noid := ooid;
  else
    select oid, qty_onorder, qty_onhand
     into noid, neworder, newhand
     from inventory
     where code = newcode;

    /* update is occurring because of constraint trigger if following is
       true, thus set oids equal */
    if ooid is null then
      ooid := noid;
      oldorder := neworder;
      oldhand := newhand;
    end if;
  end if;

  if ooid <> noid then
    oldorder := oldorder + OLD.qty;
    neworder := neworder - NEW.qty;
    oldhand := oldhand - OLD.qty;
    newhand := newhand + NEW.qty;

    update inventory set
     qty_onorder = neworder,
     qty_onhand = newhand
     where oid = noid;
  else
    if TG_OP = ''INSERT'' then
      oldorder := oldorder - NEW.qty;
      oldhand := oldhand + NEW.qty;
    else if TG_OP = ''UPDATE'' then
      oldorder := oldorder + OLD.qty - NEW.qty;
      oldhand := oldhand - OLD.qty + NEW.qty;
    else if TG_OP = ''DELETE'' then
      oldorder := oldorder + OLD.qty;
      oldhand := oldhand - OLD.qty;
    else
      raise exception ''do not understand TG_OP = %'', TG_OP;
    end if; end if; end if;
  end if;

  update inventory set
   qty_onorder = oldorder,
   qty_onhand = oldhand
   where oid = ooid;

  return null;
end;
' language 'plpgsql';

create trigger order_items
 after insert or update or delete
 on order_items
 for each row
 execute procedure order_items ();