A data base trigger is an Oracle PL/SQL program that resides in the data base. It is only executed (fired) when the event that it is programed for occurs. The firing of the trigger occurs after all the other processing has been performed against the data and just at the moment prior (a BEFORE trigger) or just subsequent (an AFTER trigger) to the data base event. The events may be one or more of: INSERT (adding), UPDATE or DELETE.
If there are multiple triggers that are executed for the same event, there is no means to control which one is executed first (i.e. two BEFORE INSERT triggers - one for referential integrity and one for workflow)
Within the Personality application, triggers are used as noted:
BEFORE INSERT#
These triggers, identified by a naming convention of P2K_xxx_BRI (where xxx is the table alias) are used to perform the following tasks:- Ensure an ID value is created from the appropriate data base sequence, if not provided;
- Set values for CREATE_DATE and CREATE_USER;
- Do referential integrity checks on lexicon values (not automatically handled by the data base as there are not foreign key constraints;
- Perform other editing and formatting tasks (i.e. upshifting any CODE values) as necessary
BEFORE UPDATE#
These triggers, identified by a naming convention of P2K_xxx_BRU (where xxx is the table alias) are used to perform the following tasks:- Set values for CHANGE_DATE and CHANGE_USER;
- Do referential integrity checks on lexicon values (not automatically handled by the data base as there are not foreign key constraints;
- Ensure non-transferrable constraints are enforced
- Perform other editing and formatting tasks (i.e. upshifting any CODE values) as necessary
BEFORE DELETE#
These triggers, identified by a naming convention of P2K_xxx_BRD (where xxx is the table alias) are used to perform the following tasks:- Conduct referential integrity checks to ensure that the record being deleted is not referenced elsewhere. Note that this is currently only used for P2K_AM_LEXICON_VALUES (MLV) as all other referential integrity constraints on foreign key links will be handled by the data base engine.