Triggers
The database triggers are used to create history data.
Every time a transaction modifies an operational table, the trigger on the table will be fired, and it will add a record in its history table. The h_create_dt has the same value as the last_update_dt in the base table. A record in the history table is considered current when the h_end_dt has no value. In other words, the historical record has not yet been ended.
If the triggers shipped with the InfoSphere® MDM are modified or dropped, there can be a significant impact on both history inquiry functions and the retrieval of the transaction audit log. Each client should decide if history should be kept. If you decide not to keep history, the triggers should be dropped to optimize performance.
Two types of triggers are provided with the InfoSphere MDM: Simple triggers and Compound triggers. You can select one of these types of trigger to install based on you own business request.
- Simple triggers create a copy of the before image
of the current data to the history table when a operational table
is updated or deleted. The history table contains only old images;
it does not contain the current image of operational table. If you
choose to install simple triggers, only update triggers will be created,
no insert triggers. You can also install the simple delete triggers,
which are optional.Advantage: Faster when you create new records. Save space, especially in large volume of data.Disadvantage: No PIT query supported, since no full image of data stored in history table
- Compound triggers create a copy of the before and
after images of the current data from the operational table to the
history table when a base table is inserted, updated or deleted. The
history table contains all old images as well as the current image
of operational table. If you choose to install compound triggers,
the insert and update triggers will be created. You can also install
the compound delete triggers, which are optional. Advantage: Full image of data in history table, which supports PIT queries and auditing.Disadvantage: Slower when create new records. State more space in DB and hard drive.