logging - Triggers execution order inside transaction for change tracking - Stack Overflow

admin管理员组

文章数量:1403516

I have a Firebird table Orders with bunch of columns. I have after update triggers that log table information in events_history table in the following manner:

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;
begin
  /* Trigger text */
  evnt_id = gen_id(events_history_gen, 1);

  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)
  values (:evnt_id, uuid_to_char(gen_uuid()),  'ORDERS', 'ORDER_ID', 'I', old.UNIT_ID, old.UNIT_UID, 'insert information', current_timestamp, current_user,  current_transaction);
end
^

SET TERM ; ^


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;
begin
  /* Trigger text */
  evnt_id = gen_id(events_history_gen, 1);

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

SET TERM ; ^

In the events history table I found something interesting. Insert log comes before update log in the same transaction for one row. Two records have equal transaction_id and equal row_id, but event_id for the update log is less than event_id for insert log. How is that possible? It seems that insert then update was executed inside transaction for one and the same record but triggers logged update then insert. I need them to be logged properly: log insert then update. How can I fix that? Why event_id generator doesn't guarantee execution order of the triggers? How can I guarantee that consecutive updates for one and the same object will be logged as they follows in the transaction block?

I have a Firebird table Orders with bunch of columns. I have after update triggers that log table information in events_history table in the following manner:

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;
begin
  /* Trigger text */
  evnt_id = gen_id(events_history_gen, 1);

  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)
  values (:evnt_id, uuid_to_char(gen_uuid()),  'ORDERS', 'ORDER_ID', 'I', old.UNIT_ID, old.UNIT_UID, 'insert information', current_timestamp, current_user,  current_transaction);
end
^

SET TERM ; ^


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;
begin
  /* Trigger text */
  evnt_id = gen_id(events_history_gen, 1);

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

SET TERM ; ^

In the events history table I found something interesting. Insert log comes before update log in the same transaction for one row. Two records have equal transaction_id and equal row_id, but event_id for the update log is less than event_id for insert log. How is that possible? It seems that insert then update was executed inside transaction for one and the same record but triggers logged update then insert. I need them to be logged properly: log insert then update. How can I fix that? Why event_id generator doesn't guarantee execution order of the triggers? How can I guarantee that consecutive updates for one and the same object will be logged as they follows in the transaction block?

• logging
• triggers
• firebird
• cdc
• order-of-execution

Share Improve this question Follow edited Mar 21 at 7:27 Mark Rotteveel 110k229229 gold badges156156 silver badges224224 bronze badges asked Mar 20 at 17:31 zloodeykazloodeyka 533 bronze badges 3

• 1 It looks like you have an "after insert" trigger that updates just inserted record and its position is less than 11000. Could you look for one to confirm? – user13964273 Commented Mar 20 at 22:12
• @user13964273 Yes, there is an "after insert" trigger with position that is less than 11000 and it updates this row. Thank you very much! – zloodeyka Commented Mar 21 at 6:11
• @zloodeyka That sounds like that should be a BEFORE INSERT that modifies the NEW context instead of an AFTER INSERT that updates the record itself. – Mark Rotteveel Commented Mar 21 at 7:26

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Since you have an AFTER INSERT trigger that updates the row that was just inserted, the sequence of events is as follows:

1. The row is inserted

2. The AFTER INSERT trigger fires and performs an UPDATE.

3. The AFTER UPDATE trigger fires and logs the update

4. The AFTER INSERT trigger fires and logs the insert

As a result, you see the update before the insert in the log.

To resolve this issue, you need to set the trigger positions so that the logging trigger fires before the updating trigger.

本文标签： loggingTriggers execution order inside transaction for change trackingStack Overflow