Firebird - get all modified fields inside a trigger

You need some meta programming, but with triggers on system tables that's no problem.

This solution seems to work, even if you have lots of columns.

set term ^ ;create or alter procedure create_audit_update_trigger (tablename char(31)) as    declare sql blob sub_type 1;    declare fn char(31);    declare skip decimal(1);begin    -- TODO add/remove fields to/from audit table    sql = 'create or alter trigger ' || trim(tablename) || '_audit_upd for ' || trim(tablename) || ' after update as begin if (';    skip = 1;    for select rdb$field_name from rdb$relation_fields where rdb$relation_name = :tablename into :fn do    begin        if (skip = 0) then sql = sql || ' or ';        sql = sql || '(old.' || trim(:fn) || ' is distinct from new.' || trim(:fn) || ')';        skip = 0;    end    sql = sql || ') then insert into ' || trim(tablename) || '_audit (';    skip = 1;    for select rdb$field_name from rdb$relation_fields where rdb$relation_name = :tablename into :fn do    begin        if (skip = 0) then sql = sql || ',';        sql = sql || trim(:fn);        skip = 0;    end    sql = sql || ') values (';    skip = 1;    for select rdb$field_name from rdb$relation_fields where rdb$relation_name = :tablename into :fn do    begin        if (skip = 0) then sql = sql || ',';        sql = sql || 'new.' || trim(:fn);        skip = 0;    end    sql = sql || '); end';    execute statement :sql;end ^create or alter trigger field_audit for rdb$relation_fields after insert or update or delete asbegin    -- TODO filter table name, don't include system or audit tables    -- TODO add insert trigger    execute procedure create_audit_update_trigger(new.rdb$relation_name);end ^set term ; ^

This tool is the firebirds solution for your problem:

http://www.upscene.com/products.audit.iblm_main.php

Otherwise You can't access the new./old. variables dynamically.

I investigated an execute statement based solution, but it is also a dead-end.

Using EXECUTE STATEMENT with a context variable (NEW or OLD) will never work, cause that's only available inside a trigger, not in a new statement (the EXECUTE STATEMENT) is not executed inside the trigger, although it uses the same connection and transaction.