1 DEFINE_TRIGGER Creates a trigger for the specified relation. A trigger is a mechanism which associates a set of rules with an update operation. Example: RDO> DEFINE TRIGGER EMPLOYEE_ID_CASCADE_DELETE <--- trigger name cont> BEFORE ERASE <--- action time cont> FOR E IN EMPLOYEES <--- subject relation cont> EXECUTE cont> FOR D IN DEGREES WITH <--- triggered action rse cont> D.EMPLOYEE_ID = E.EMPLOYEE_ID cont> ERASE D <--- triggered action cont> END_FOR cont> FOR EACH RECORD. <--- frequency clause 2 More To define a trigger, you need the Oracle Rdb READ and DEFINE privileges to the subject relation. If any triggered statement specifies some form of update operation, then CONTROL and the appropriate update privilege (ERASE, MODIFY, or WRITE) to the relations specified by the triggered action statements are also required. Each trigger is associated with a single subject relation, will be evaluated at a specific time for a particular type of update on that relation, and specifies a series of 'triggered' actions. Each triggered action consists of an optional condition and one or more statements to be evaluated either once only or for each record of the relation being updated. By defining combinations of relation-specific constraints and triggers, you can help to preserve integrity for a database. However, the relation-specific constraints and triggers that you define only preserve data integrity for the fields and relations specified in the constraints and triggers, not for the entire database. You can define a trigger only after you have invoked the database. See the DEFINE_RELATION statement. You must execute this statement in a read/write transaction. If there is no active transaction and you issue this statement, Oracle Rdb starts a read/write transaction implicitly. Other users are allowed to be attached to the database when you issue the DEFINE TRIGGER statement. Triggers that update rows of the trigger subject relation or add rows to the trigger subject relation can cause infinite loops or inconsistent results to be returned, as in the following two conditions: o A BEFORE MODIFY trigger on relation X that inserts a row into relation X o A MODIFY statement affecting all the rows in relation X Considering these two conditions, the MODIFY statement will loop until all resources are consumed because for each row updated, a new row will be added, which in turn will be updated, and so forth. When subject relation rows are being retrieved using an index, there is the possibility that a triggered action operating on the same relation could affect the index (by changing index key values or adding new keys) such that the triggering statement behaves in a different manner than when there is no trigger involved. Currently, only avoidance methods can be suggested for this problem. The best way to avoid this problem is to construct any such triggers to operate only on rows that are either the current subject relation row, or that will never be selected by the triggering statement. A more difficult avoidance method is to restructure triggering statements such that they could never select a row that could have been updated or added by a trigger action. Some circumstances will require a combination of these methods. 2 Format (B)0DEFINE TRIGGER q> name wqqqqqqqqqqqqqq>qqqqqqqqqqqqqwwq> BEFORE wk m> DESCRIPTION IS /* text */ jmq> AFTER qjx lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq STORE qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwk t> ERASE qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqux m> MODIFY wqqqqqqqqqq>qqqqqqqqqqwwqqqqqqqqqqqqqq>qqqqqqqqqqqqwqjx m> OF qw> field-name wjm> context-variable-clause qj x mqqqqq , FOR context-var IN relation-name w> triggered-action-clause w> . mqqqqqqqqqqqqqq tq> OLD CONTEXT old-context-var qu mq> NEW CONTEXT new-context-var qj An optional clause that can contain aliases for old and new states of the context variable of the triggering statement. 4 old-context-var A temporary name used to refer to the record values as they existed before a modify operation occurred. You use an old- context-var only when the trigger occurs after a modify operation, and must be a different name than the one given for context_var. 4 new-context-var A temporary name used to reference the new record values about to be applied by modify operation. You use a new-context-var only when the trigger occurs before a modify operation, and must be a different name than the one given for context-var. 3 context-var A temporary name defining the relation on which the trigger is defined. The context variable name is an alias for the record stream context of the triggering statement which has caused this trigger to be executed. 3 relation-name The name of the relation for which this trigger is defined (subject relation). 3 triggered-action-clause (B)0triggered-action-clause = qwqqqqqqqqqqqq>qqqqqqqqqqqqqqwqk mq> WITH conditional-expr qqj x lqqqqqqqqqqqqq EXECUTE qw> triggered-statement qwqqwqqqqqqqqqq>qqqqqqqqwq> mqqqqqqqqqqq ; qqqq FOR EACH RECORD j (B)0triggered-statement = qwq> store-statement qqqqqqqqqqqqqqqqqqqwq> tq> FOR rse store-statement END_FOR qqu tq> FOR rse erase-statement END_FOR qqu tq> FOR rse modify-statement END_FOR qqu mq> ERROR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj A triggered action clause consists of a conditional expression (optional), one or more triggered statements, and a frequency clause (FOR EACH RECORD). The EXECUTE keyword precedes the triggered action clause. 4 rse A record selection expression that defines which records of which relations will be affected by the triggered update action. This rse cannot refer to any host variables. 4 store-statement A STORE statement to be initiated by the trigger. 4 erase-statement An ERASE statement to be initiated by the trigger. 4 modify-statement A MODIFY statement to be initiated by the trigger. 4 WITH A conditional expression that describes the optional condition that must be satisfied before the associated triggered statements are executed. This expression cannot refer to any host variables. For more information on Oracle Rdb conditional expressions, ask for HELP on the top level topic Cond_expr. 4 FOR_EACH_RECORD A frequency clause, FOR EACH RECORD (no underscores), determines whether an action is evaluated once per triggering statement, or for each record of the subject relation updated by the triggering statement. If the FOR EACH RECORD clause is not specified, the triggered action is evaluated only once, and record values are not available to the triggered action. 2 Examples The following example defines a trigger that performs a cascading delete triggered by the deletion of an employee record. Such a trigger can be used to maintain referential integrity among the EMPLOYEES, JOB_HISTORY, RESUMES, and SALARY_HISTORY relations. Each associated employee record (from the relations which have foreign keys referring to the primary key in the EMPLOYEES relation) is deleted. RDO> DEFINE TRIGGER EMPLOYEE_ID_CASCADE_DELETE cont> BEFORE ERASE cont> FOR E IN EMPLOYEES cont> EXECUTE cont> FOR D IN DEGREES WITH cont> D.EMPLOYEE_ID = E.EMPLOYEE_ID cont> ERASE D cont> END_FOR; cont> FOR JH IN JOB_HISTORY WITH cont> JH.EMPLOYEE_ID = E.EMPLOYEE_ID cont> ERASE JH cont> END_FOR; cont> FOR R IN RESUMES WITH cont> R.EMPLOYEE_ID = E.EMPLOYEE_ID cont> ERASE R cont> END_FOR; cont> FOR SH IN SALARY_HISTORY WITH cont> SH.EMPLOYEE_ID = E.EMPLOYEE_ID cont> ERASE SH cont> END_FOR cont> FOR EACH RECORD. The following RDO command procedure example defines a MODIFY trigger with two actions. The first action is defined to account for the situation where the triggering MODIFY statement has not actually changed the value for the pertinent field (EMPLOYEE_ ID). This trigger causes a cascading update of the EMPLOYEES relation's EMPLOYEE_ID value to the JOB_HISTORY table. The WITH clause stipulates that the cascading update will occur only when the EMPLOYEE_ID value actually changes. The example also logs each MODIFY operation to the LOG relation. ! ! Invoke the database: INVOKE DATABASE FILENAME 'PERSONNEL' ! ! Define the global fields for the LOG relation: START_TRANSACTION READ_WRITE ! DEFINE FIELD TYPE DATATYPE IS TEXT 10. %RDO-W-NOCDDUPDAT, database invoked by filename, the data dictionary will not be updated ! DEFINE FIELD REL_NAME DATATYPE IS TEXT 31. ! ! Define the LOG relation: DEFINE RELATION LOG. TYPE. REL_NAME. END LOG RELATION. ! ! Define trigger TRIG1: DEFINE TRIGGER TRIG1 AFTER MODIFY OF EMPLOYEE_ID OLD CONTEXT OLD_EMP FOR NEW_EMP IN EMPLOYEES WITH NEW_EMP.EMPLOYEE_ID <> OLD_EMP.EMPLOYEE_ID EXECUTE FOR JH IN JOB_HISTORY WITH JH.EMPLOYEE_ID = OLD_EMP.EMPLOYEE_ID MODIFY JH USING JH.EMPLOYEE_ID = NEW_EMP.EMPLOYEE_ID END_MODIFY END_FOR FOR EACH RECORD EXECUTE STORE L IN LOG USING L.TYPE = "Modify"; L.REL_NAME = "EMPLOYEES" END_STORE FOR EACH RECORD. ! ! Test the trigger by changing the EMPLOYEE_ID of "00164" to "98765": FOR E IN EMPLOYEES WITH E.EMPLOYEE_ID = "00164" MODIFY E USING E.EMPLOYEE_ID = "98765" END_MODIFY END_FOR ! ! The trigger causes a record to be stored in the LOG relation: FOR L IN LOG PRINT L.TYPE, L.REL_NAME END_FOR TYPE REL_NAME Modify EMPLOYEES !