事务内变更追踪的Firebird触发器执行顺序问题

解决方案 #

要保证同一事务内操作日志的顺序，需结合事务ID和事务内操作序号排序，而非单独依赖生成器的event_id。具体实现步骤如下：

1. 修改events_history表结构

新增operation_sequence字段，用于记录同一事务内的操作顺序：

ALTER TABLE events_history ADD COLUMN operation_sequence INTEGER;

2. 更新触发器代码

利用Firebird的USER_SESSION上下文变量跟踪事务内的操作序号，每次触发触发器时自动递增：

AFTER INSERT触发器

SET TERM ^ ;
CREATE OR ALTER TRIGGER ORG_STRUCTURE_EVNT_HST_AI FOR ORG_STRUCTURE
ACTIVE AFTER INSERT POSITION 11000
AS
  DECLARE VARIABLE evnt_id INTEGER;
  DECLARE VARIABLE op_seq INTEGER;
BEGIN
  evnt_id = gen_id(events_history_gen, 1);
  
  -- 初始化或递增事务内操作序号
  IF (RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') IS NULL) THEN
    RDB$SET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ', 1);
  ELSE
    RDB$SET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ', CAST(RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') AS INTEGER) + 1);
  
  op_seq = CAST(RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') AS INTEGER);

  -- 注意：INSERT触发器需使用NEW字段获取新插入行的值
  INSERT INTO events_history(
    event_id, event_uid, table_name, pk_name, type_op, 
    row_id, row_uid, new_value_json, event_date, username, 
    transaction_id, operation_sequence
  ) VALUES (
    :evnt_id, UUID_TO_CHAR(GEN_UUID()), 'ORDERS', 'ORDER_ID', 'I',
    NEW.UNIT_ID, NEW.UNIT_UID, 'insert information', CURRENT_TIMESTAMP, CURRENT_USER,
    CURRENT_TRANSACTION, :op_seq
  );
END
^

SET TERM ; ^

AFTER UPDATE触发器

SET TERM ^ ;
CREATE OR ALTER TRIGGER ORG_STRUCTURE_EVNT_HST_AU FOR ORG_STRUCTURE
ACTIVE AFTER UPDATE POSITION 12000
AS
  DECLARE VARIABLE evnt_id INTEGER;
  DECLARE VARIABLE op_seq INTEGER;
BEGIN
  evnt_id = gen_id(events_history_gen, 1);
  
  -- 初始化或递增事务内操作序号
  IF (RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') IS NULL) THEN
    RDB$SET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ', 1);
  ELSE
    RDB$SET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ', CAST(RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') AS INTEGER) + 1);
  
  op_seq = CAST(RDB$GET_CONTEXT('USER_SESSION', 'TRX_OP_SEQ') AS INTEGER);

  INSERT INTO events_history(
    event_id, event_uid, table_name, pk_name, type_op, 
    row_id, row_uid, new_value_json, event_date, username, 
    transaction_id, operation_sequence
  ) VALUES (
    :evnt_id, UUID_TO_CHAR(GEN_UUID()), 'ORDERS', 'ORDER_ID', 'U',
    OLD.UNIT_ID, OLD.UNIT_UID, 'update information', CURRENT_TIMESTAMP, CURRENT_USER,
    CURRENT_TRANSACTION, :op_seq
  );
END
^

SET TERM ; ^

3. 按正确顺序查询日志

查询时通过transaction_id分组，再按operation_sequence排序，即可还原事务内的操作顺序：

SELECT * FROM events_history
ORDER BY transaction_id, operation_sequence;

为什么event_id生成器无法保证触发器执行顺序？ #

Firebird生成器是全局共享对象，gen_id的原子性仅保证全局递增，但无法保证同一事务内的操作序号连续或按执行顺序排列：

• 其他事务的gen_id调用会插入到当前事务的调用序列中，导致同一事务内的event_id可能不连续。
• 生成器默认启用缓存机制（默认缓存值为20），并发场景下会一次性分配一批值给不同事务，可能出现其他事务的event_id插入到当前事务的操作序列中；你遇到的顺序颠倒，大概率是并发事务干扰或触发器绑定表误配（代码中触发器针对ORG_STRUCTURE表，但日志记录ORDERS表）导致的特殊情况。

如何确保同一对象的连续更新操作日志按事务内执行顺序记录？ #

除了上述事务ID+操作序号方案，还可通过以下方式强化顺序保证：

• 使用微秒级时间戳：Firebird 4.0及以上支持TIMESTAMP(6)（微秒精度），将event_date字段改为该类型，同一事务内的操作时间戳会有细微差异，可作为排序辅助依据。
• 确保操作在同一事务内执行：Firebird的USER_SESSION上下文变量仅在当前事务内有效，能准确跟踪事务内的操作顺序。
• 校验触发器绑定表：确认触发器绑定的表与操作表一致，避免因表绑定错误导致的触发器执行顺序混乱。

内容的提问来源于stack exchange，提问作者zloodeyka