Extended History Logging

The default history logging functionality in FTM occurs only when an object status changes. A client may wish to track all changes to the payment details. This may be implemented by creating triggers on the other operational data tables. The AUDIT_SEQ column within these tables allows changes to be linked among:

The object history record
The associated history record for the transmission, batch, transaction, and so on
The object value history record (if available)

The AUDIT_SEQ column in the OBJ_BASE table is always initialized to 1. Trigger T_ST_DAT, on table OBJ_BASE, increments the contents of AUDIT_SEQ each time the object status changes. This "labels" each status change. When any update happens to the associated records in the TRANSMISSION_BASE, BATCH_BASE, TRANSACTION_BASE, or other tables, the defined trigger will update its AUDIT_SEQ with the current value of AUDIT_SEQ from the OBJ_BASE table.

To disable history logging for a record, set AUDIT_SEQ to 0.

This example shows triggers to log the history of TRANSACTION_BASE record changes:

CREATE TRIGGER FTM.T_TR_DAT
     NO CASCADE
     BEFORE UPDATE ON FTM.TRANSACTION_BASE
     REFERENCING NEW AS n  OLD AS o
     FOR EACH ROW
     MODE DB2SQL
     WHEN (o.AUDIT_SEQ > 0)
BEGIN ATOMIC
     SET n.AUDIT_SEQ =
            (SELECT AUDIT_SEQ FROM FTM.OBJ_BASE WHERE ID=o.OBJ_ID);
END@

DROP TRIGGER FTM.T_TR_HIS@
CREATE TRIGGER FTM.T_TR_HIS
     AFTER UPDATE ON FTM.TRANSACTION_BASE
     REFERENCING OLD AS o  NEW AS n
     FOR EACH ROW
     MODE DB2SQL
     WHEN ((o.AUDIT_SEQ > 0) and (o.AUDIT_SEQ <> n.AUDIT_SEQ))
BEGIN ATOMIC
     INSERT INTO FTM.H_TRANSACTION_BASE (OBJ_ID, BATCH_ID,
            TRANSMISSION_ID, ALT_ID,
            ISF_DATA, USER,
            USER_COMMENT, TXN_DATA1, TXN_DATA2,
            TXN_SEQUENCE, AUDIT_SEQ)
     VALUES (o.ID, o.OBJ_ID, o.BATCH_ID,
             o.TRANSMISSION_ID, o.ALT_ID, o.ISF_DATA,
             o.USER, o.USER_COMMENT,
             o.TXN_DATA1, o.TXN_DATA2, o.TXN_SEQUENCE,
             o.AUDIT_SEQ);
END@

This example illustrates best-practice suggestions for the extended history logging:

Triggers take no action if the AUDIT_SEQ column for the record is set to 0.
No history record is created when the old AUDIT_SEQ is identical to the new AUDIT_SEQ. The new AUDIT_SEQ is the current AUDIT_SEQ in the OBJ_BASE table. This is to prevent having two history records for the same ID and the same AUDIT_SEQ. (This could only possibly happen when there is more than one update on the same record in the same action.)