我需要获得连续更改的所有值,并在其他“审核”表上发布修改.我可以完成这个,没有为行中的每个元素写条件？我知道来自
http://www.firebirdfaq.org/faq133/的sql,它为您提供了验证的所有条件：
- 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';
但这应该写在触发器中.所以,如果我更改一个表,那么我需要修改触发器.
由于FireBird不允许动态增加varchar变量的大小,所以在将它插入到文本blob之前,我正在考虑将所有值转换并连接到一个大的varchar变量.
有没有可能完成这个,没有使用GTTs？
解决方法
您需要一些元编程,但是系统表上的触发器没有问题.
这个解决方案似乎有效,即使你有很多列.
- 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 ; ^
