Firebird-在触发器中获取所有修改后的字段 [英] Firebird - get all modified fields inside a trigger

问题描述

我需要获取所有连续更改的值，并将修改后的值发布到其他审核表中。我可以在不编写行中每个元素的条件的情况下完成此操作吗？我从 http://www.firebirdfaq.org/faq133/ 了解SQL验证条件：

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.

由于FireBird不允许动态地增加varchar变量的大小这一事实，因此我在插入之前考虑将所有值强制转换并串联到一个大的varchar变量中

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.

是否有可能无需使用 GTT ？

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

推荐答案

您需要一些元编程，但是在系统表上使用触发器没有问题。

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

这篇关于Firebird-在触发器中获取所有修改后的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！