set term ^;
execute block returns(trg_text varchar(150)) as
declare our_max_char int = 2048; -- suppose as maximal length of any (var)char field in our database
declare tab_name varchar(31);
declare fld_name varchar(31);
declare is_blob int;
declare trg_prefix varchar(2)='t$';
declare tab_start varchar(31) = '';
declare fld_cnt int;
declare fld_idx int = 0;
begin
  for
    with
    c as(
        select rf.rdb$relation_name tab_name, rf.rdb$field_name fld_name, iif(ff.rdb$field_type=261,1,0) is_blob
        from rdb$relation_fields rf
        join rdb$fields ff on rf.rdb$field_source = ff.rdb$field_name
        left join (
            select rc.rdb$relation_name tab_name, rs.rdb$field_name fld_name
            from rdb$relation_constraints rc
            join rdb$index_segments rs on rc.rdb$index_name = rs.rdb$index_name
            where rc.rdb$constraint_type='PRIMARY KEY'
        ) pk on rf.rdb$relation_name=pk.tab_name and  rf.rdb$field_name = pk.fld_name
        where pk.tab_name is null -- exclude fields that forms primary key in tables
              and rf.rdb$system_flag =0
              and ff.rdb$computed_source is null
    )
    select d.tab_name,d.fld_cnt,c.fld_name, c.is_blob
    from(
        select c.tab_name,count(c.fld_name) fld_cnt -- need for stop generating trigger text after last field
        from c
        group by c.tab_name
    ) d
    join c on d.tab_name=c.tab_name
    order by tab_name,fld_name
  into
    tab_name, fld_cnt, fld_name, is_blob
  do
  begin
    if (fld_idx=0) then --  (tab_name <> tab_start) then
    begin
        tab_start = tab_name;
        trg_text='set term ^;'; suspend;
        trg_text='create or alter trigger '||trg_prefix||trim(left(lower(tab_name),31-char_length(trg_prefix)))
                 ||' for '||trim(lower(tab_name)) || ' active after insert or update position 32760 as'; suspend;
        trg_text='begin'; suspend;
        trg_text='  --- ### GENERATED AUTO! DO NOT EDIT! ###'; suspend;
        trg_text='  --- ### COMPUTED AND PRIMARY KEY FIELDS EXCLUDED ###'; suspend;
    end

    trg_text='  if (old.'||trim(lower(fld_name))||' is distinct from new.'||trim(lower(fld_name))||') then '; suspend;
    if (is_blob=0) then
    begin
        trg_text='    insert into audit_table(tab_name, fld_name, old_value, new_value)'; suspend;
        trg_text='    values('''||trim(lower(tab_name))||''''
                          ||','''||trim(lower(fld_name))||''','
                          ||' cast(old.'||trim(lower(fld_name))||' as varchar('||our_max_char||')),'
                          ||' cast(new.'||trim(lower(fld_name))||' as varchar('||our_max_char||'))'
                          ||');';
    end
    else begin
        trg_text='    insert into audit_table(tab_name, fld_name, old_blob, new_blob)'; suspend;
        trg_text='    values('''||trim(lower(tab_name))||''''
                          ||','''||trim(lower(fld_name))||''','
                          ||' old.'||trim(lower(fld_name))||','
                          ||' new.'||trim(lower(fld_name))
                          ||');';
    end
    suspend;
    fld_idx=fld_idx+1;
    if (fld_idx=fld_cnt) then
    begin
        trg_text='end^'; suspend;
        trg_text='set term ;^;'; suspend;
        trg_text='commit;'; suspend;
        fld_idx=0;
    end
  end
end^
set term ;^
commit;

set term ^;
create or alter trigger t$tblob for tblob active after insert or update position 32760 as
begin
  --- ### GENERATED AUTO! DO NOT EDIT! ###
  --- ### COMPUTED AND PRIMARY KEY FIELDS EXCLUDED ###
  if (old.b is distinct from new.b) then
    insert into audit_table(tab_name, fld_name, old_blob, new_blob)
    values('tblob','b', old.b, new.b);
end^
set term ;^;
commit;
set term ^;
create or alter trigger t$tcalc for tcalc active after insert or update position 32760 as
begin
  --- ### GENERATED AUTO! DO NOT EDIT! ###
  --- ### COMPUTED AND PRIMARY KEY FIELDS EXCLUDED ###
  if (old.x is distinct from new.x) then
    insert into audit_table(tab_name, fld_name, old_value, new_value)
    values('tcalc','x', cast(old.x as varchar(2048)), cast(new.x as varchar(2048)));
  if (old.y is distinct from new.y) then
    insert into audit_table(tab_name, fld_name, old_value, new_value)
    values('tcalc','y', cast(old.y as varchar(2048)), cast(new.y as varchar(2048)));
end^
set term ;^;
commit;