CREATE TABLE TABLE_AAA (
    ID INTEGER NOT NULL,
    NAME VARCHAR(20));
    
ALTER TABLE TABLE_AAA ADD CONSTRAINT PK_TABLE_AAA PRIMARY KEY (ID);

CREATE TABLE TABLE_BBB (
    ID INTEGER NOT NULL,
    AAA_ID integer NOT NULL,
    DBL DOUBLE PRECISION);
    
ALTER TABLE TABLE_BBB ADD CONSTRAINT PK_TABLE_BBB PRIMARY KEY (ID);

ALTER TABLE TABLE_BBB ADD CONSTRAINT FK_TABLE_BBB_AAA FOREIGN KEY (AAA_ID) REFERENCES TABLE_AAA(ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TRIGGER TABLE_BBB_BI0 FOR TABLE_BBB
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  new.ID = (select max(ID) from TABLE_BBB) + 1;
  if (new.ID is null) then
    new.ID = 1;
end;


CREATE OR ALTER VIEW TABLE_AAA_VIEW(
    ID,
    DBL)
AS
  select
    A.ID,
    case
      when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
      else A.ID * 10
    end DBL
  from
    TABLE_AAA A
  order by
    A.ID
;

create procedure TABLE_AAA_PROC
returns (
    ID integer,
    DBL double precision)
as
begin
  for
    select
      A.ID,
      case
        when A.ID > 2 then (select sum(B.DBL) from TABLE_BBB B where B.AAA_ID < A.ID)
        else A.ID * 10
      end DBL
    from
      TABLE_AAA A
    into
      :ID, :DBL
  do
  begin
    suspend;
  end
end



commit work;

insert into TABLE_AAA (ID, NAME) values ( 1, '1');
insert into TABLE_AAA (ID, NAME) values ( 2, '2');
insert into TABLE_AAA (ID, NAME) values ( 3, '3');
insert into TABLE_AAA (ID, NAME) values ( 4, '4');
insert into TABLE_AAA (ID, NAME) values ( 5, '5');
insert into TABLE_AAA (ID, NAME) values ( 6, '6');
insert into TABLE_AAA (ID, NAME) values ( 7, '7');
insert into TABLE_AAA (ID, NAME) values ( 8, '8');
insert into TABLE_AAA (ID, NAME) values ( 9, '9');
insert into TABLE_AAA (ID, NAME) values (10, '10');




commit work;