ALLBASE/SQL Reference Manual (36216-90216)
348 Chapter10
SQL Statements A - D
CREATE RULE
Description
• A rule may be created through ISQL or through an application program.
• When a rule is created, information about the rule is stored in the system catalog, and
may be examined through the following system views: SYSTEM.RULE,
SYSTEM.RULECOLUMN, and SYSTEM.RULEDEF.
• The
FiringCondition
and
ParameterValue
can reference both the unchanged and
the changed values of the row being considered for the firing of a rule. The unchanged
values are known as
old
values and are referred to by using the
OldCorrelationName.
Changed values are known as
new
values and are referred to
by using the
NewCorrelationName
.
• For an INSERT, there is no old value to reference, so the use of
OldCorrelationName
will be treated as if
NewCorrelationName
had been specified.
• For a DELETE, there is no new value to reference, so the use of
NewCorrelationName
will be treated as if
OldCorrelationName
had been specified.
• If no
OldCorrelationName
is defined, OLD is the default.
• If no
NewCorrelationName
is defined, NEW is the default.
• At most one
OldCorrelationName
and one
NewCorrelationName
can be specified.
• Use of the
TableName
has the same effect as use of the
NewCorrelationName
if the
StatementType
is INSERT or UPDATE. Use of the
TableName
has the same effect as
use of the
OldCorrelationName
if the
StatementType
is DELETE.
•
NewCorrelationName
and
OldCorrelationName
must differ from each other. If
either is the same as the
TableName
, then the correlation name will be assumed to be
used wherever that name qualifies a column reference without an owner qualification
also being used. If the table is called OLD, reference it by using
OwnerName
.OLD.
ColumnName
.
• Rules can execute in a forward-chaining manner. This occurs when a fired rule invokes
a procedure which contains a statement that causes other rules to fire. The maximum
nesting of rule invocations is 20 levels.
• If multiple rules are to be fired by a given statement, the order in which the rules fire
may change when the section is revalidated. You can use the SET PRINTRULES ON
statement to generate messages giving the names of rules as they fire.
• If an error occurs during the execution of a rule or its invoked procedure, it will have its
normal effect, that is, a message may be generated, the execution of the statement may
be halted, the effects of the statement may be rolled back, or the connection may be lost.
Even if the error has not caused the transaction to roll back or the connection to be lost,
the statement issued by the user and all rules fired on behalf of that statement (or
chained to by such rules) are undone and have no effect on the database.
• The procedure invoked by a rule cannot execute a COMMIT WORK, ROLLBACK WORK,
COMMIT/ROLLBACK ARCHIVE, or SAVEPOINT statement. If the procedure executes one
of these statements, an error occurs, and the effect of the statement that triggered the
procedure is undone.