Firebird - get all modified fields inside a trigger

Question

I need to get all the values which changed in a row and post modifications on other 'audit' table. Can I accomplish this, without writing the conditions for each element from the row? I know the SQL from http://www.firebirdfaq.org/faq133/ which gives you all the conditions for verifications:

select 'if (new.' || rdb$field_name || ' is null and old.' ||
rdb$field_name || ' is not null or new.' || rdb$field_name ||
'is not null and old.' || rdb$field_name || ' is null or new.' ||
rdb$field_name || ' <> old.' || rdb$field_name || ') then'
from rdb$relation_fields
where rdb$relation_name = 'EMPLOYEE';

but this should be written in the trigger. So, if I change a table then I need to modify the trigger.

Due the fact that FireBird does not allow dynamically increasing the size of a varchar variable I was thinking of casting and concatenating all the values to a big varchar variable, before inserting it in a text blob.

Is there any possibility to accomplish this, without using GTTs?

Chloramphenicol

Answer 1

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 as
begin
    -- 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 ; ^

Thyrse

+1 for inventiveness ... but does it work? This mailing list thread suggests that ① the system table triggers vanish when some table is DROPped, and ② this approach at least used to be crashy. – Gainey

Answer 2

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.

Oswell

I can not use another tool. It needs to be made 'in-house'. Thank you. – Chloramphenicol

In this case, I think the best way to create a few stored procedures to regenerate the triggers, and schedule them to run on a daily base (or when you run DDL-s on your database). – Oswell