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
1 – 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)0[m[4mDEFINE[m [4mTRIGGER[m q> name wqqqqqqqqqqqqqq>qqqqqqqqqqqqqwwq> [4mBEFORE[m wk
m> [4mDESCRIPTION[m IS /* text */ jmq> [4mAFTER[m qjx
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mw> [4mSTORE[m qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwk
t> [4mERASE[m qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqux
m> [4mMODIFY[m wqqqqqqqqqq>qqqqqqqqqqwwqqqqqqqqqqqqqq>qqqqqqqqqqqqwqjx
m> [4mOF[m qw> field-name wjm> context-variable-clause qj x
mqqqqq , <qqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
m> [4mFOR[m context-var [4mIN[m relation-name w> triggered-action-clause w> .
mqqqqqqqqqqqqqq<qqqqqqqqqqqj
2.1 – name
The name of the trigger that you can refer to in other
statements. Use a name that is unique among all trigger names.
2.2 – text
A text string that adds a comment to the trigger definition.
2.3 – field-name
The name of a field within the specified relation to be checked
for modification. You can specify a list of field names separated
by commas. You can also specify all the fields in a relation by
specifying the asterisk (*) wildcard character.
Be sure that you specify ONLY those fields whose data values are
to be changed. See the subtopic More for more information.
2.3.1 – More
In a BEFORE MODIFY or AFTER MODIFY trigger, be sure that you
specify only the names of fields whose data values are to be
changed, to avoid potentially unnecessary actions such as:
o Overlaying the data with itself within a record.
o Writing to the database (even though none of the fields in the
record has actually changed values)
o Evaluating constraints that apply to fields in the MODIFY
field list which have not changed values
o Evaluating MODIFY triggers that apply to fields in the MODIFY
field list that have not changed values.
o Evaluating RDO VALID IF clauses for fields in the MODIFY field
list that have not changed values.
If there is a possibility that any of the fields in a MODIFY
field list will not actually be changed, the triggered actions
for any pertinent MODIFY triggers should be changed accordingly.
For those cases in which a triggered action performs an operation
based on the changed value for a particular field, the action
should include a conditional expression that prevents execution
of the operation if no value change occurs. The conditional
expression, which compares the old value with the new value,
should appear as part of the triggered action's WITH clause.
2.4 – context-var-clause
(B)0[mcontext-variable-clause =
qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
tq> [4mOLD[m CONTEXT old-context-var qu
mq> [4mNEW[m 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.
2.4.1 – 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.
2.4.2 – 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.
2.5 – 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.
2.6 – relation-name
The name of the relation for which this trigger is defined
(subject relation).
2.7 – triggered-action-clause
(B)0[mtriggered-action-clause =
qwqqqqqqqqqqqq>qqqqqqqqqqqqqqwqk
mq> [4mWITH[m conditional-expr qqj x
lqqqqqqqqqqqqq<qqqqqqqqqqqqqqqj
mq> [4mEXECUTE[m qw> triggered-statement qwqqwqqqqqqqqqq>qqqqqqqqwq>
mqqqqqqqqqqq ; qqqq<qqqqj mq> [4mFOR[m [4mEACH[m [4mRECORD[m j
(B)0[mtriggered-statement =
qwq> store-statement qqqqqqqqqqqqqqqqqqqwq>
tq> [4mFOR[m rse store-statement [4mEND_FOR[m qqu
tq> [4mFOR[m rse erase-statement [4mEND_FOR[m qqu
tq> [4mFOR[m rse modify-statement [4mEND_FOR[m qqu
mq> [4mERROR[m 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.
2.7.1 – 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.
2.7.2 – store-statement
A STORE statement to be initiated by the trigger.
2.7.3 – erase-statement
An ERASE statement to be initiated by the trigger.
2.7.4 – modify-statement
A MODIFY statement to be initiated by the trigger.
2.7.5 – 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.
2.7.6 – 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.
3 – 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
!