CREATE TABLE ORDERED_ITEMS (
    ID INTEGER NOT NULL,
    ID_GOODS INTEGER NOT NULL,
    QUANTITY INTEGER NOT NULL,
    ORDER_AMOUNT INTEGER NOT NULL,
    ID_ORDER INTEGER NOT NULL)

CREATE TABLE ORDERS (
    ID INTEGER NOT NULL,
    ID_STAFF INTEGER NOT NULL,
    DATETIME "DATE",
    ORDER_AMOUNT INTEGER)

create or alter procedure MAKE_ORDER_AMOUNT (
    QUANTITY integer not null,
    PRICE integer not null,
    LAST_ORDER_AMOUNT integer not null)
returns (
    NEW_ORDER_AMOUNT integer)
as
begin
  NEW_ORDER_AMOUNT=LAST_ORDER_AMOUNT+(PRICE*QUANTITY);
  suspend;
end

CREATE OR ALTER trigger ordered_items_bi for ordered_items
active before insert position 0
as
declare variable result integer;
declare variable orderamount integer;
begin
  if (new.id is null) then
    new.id = gen_id(gen_ordered_items_id,1);
  orderamount = (select orders.order_amount from ordered_items, orders where new.id_order = orders.id);
  execute procedure make_order_amount(new.quantity,new.price,orderamount)returning_values(result);
  update orders set order_amount = result where new.id_order = orders.id;
end

as
declare variable result integer;
declare variable orderamount integer;
begin
  if (new.id is null) then
    new.id = gen_id(gen_ordered_items_id,1);
  orderamount = (select first 1 orders.order_amount from ordered_items, orders where new.id_order = orders.id);
  execute procedure make_order_amount(new.quantity,new.price,orderamount)returning_values(:result);
  update orders set order_amount = :result where new.id_order = orders.id;
end