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.

LOGGING TRIGGERS#

These triggers are optionally defined and are identified by a naming conventions P2K_xx_LOG_TRG (where xxx is the table alias) are used to extend the audit logging facility within the application to capture changes made externally to the application. The actual trigger fires after the change has been committed to the data base (AFTER INSERT, UPDATE, DELETE). Triggers are created by the am_xmlt.sql script and dropped using the db_drop_logging_triggers.sql script.

WORKFLOW TRIGGERS#

These triggers, identified by a naming convention of P2K_xxx_BIUD (where xxx is the table alias) are used to control the processing of workflow, if defined. The trigger fires after all the data has been manipulated, but just prior to the actual commit to the data base (BEFORE INSERT, UPDATE, DELETE). Triggers are created by the seed_create_BIUD.sql script and dropped using the db_drop_wf_triggers.sql script.

CACHING TRIGGERS#

These triggers, identified by a naming convention of P2K_xxx_AIUD (where xxx is the table alias) are used to enable the application server to cache the changes to records. The actual trigger fires after the change has been committed to the data base (AFTER INSERT, UPDATE, DELETE). See the article on caching for further details on how this works. These triggers are created by the db_generate_monitor_triggers.sql script and dropped by the db_drop_monitor_triggers.sql script.

---

Notes #

Click to create a new notes page