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|REFERENTIAL INTEGRITY] and one for [workflow|MODULE-WF]) Within the [{$applicationname}] 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|TABLE_ALIAS]) are used to perform the following tasks: * Ensure an ID value is created from the appropriate [data base sequence|DATA BASE SEQUENCE], if not provided; * Set values for [CREATE_DATE] and [CREATE_USER]; * Do [referential integrity|REFERENTIAL INTEGRITY] checks on lexicon values (not automatically handled by the data base as there are not [foreign key|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|TABLE_ALIAS]) are used to perform the following tasks: * Set values for [CHANGE_DATE] and [CHANGE_USER]; * Do [referential integrity|REFERENTIAL INTEGRITY] checks on lexicon values (not automatically handled by the data base as there are not [foreign key|FOREIGN KEY] constraints; * Ensure [non-transferrable|NON-TRANSFERABLE] 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|TABLE_ALIAS]) are used to perform the following tasks: * Conduct [referential integrity|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|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|TABLE_ALIAS]) are used to extend the [audit logging|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|TABLE_ALIAS]) are used to control the processing of [workflow|MODULE-WF], 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) !CACHING TRIGGERS These triggers, identified by a naming convention of __P2K_xxx_AIUD__ (where xxx is the [table alias|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|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|Edit:Internal.DATA+BASE+TRIGGER] [{InsertPage page='Internal.DATA+BASE+TRIGGER' default='Click to create a new notes page'}]