|
|
The CREATE RULE statement defines a rule and associates it with specific
kinds of data manipulation on a particular table. The rule definition
specifies the name of a procedure to be executed when the rule fires.
ISQL or Application Programs
CREATE RULE [Owner.]RuleName
AFTER StatementType [,...][ON
OF
FROM
INTO }[Owner.]TableName
[REFERENCING {OLD AS OldCorrellationName
NEW AS NewCorrelationName}[...]]
[WHERE FiringCondition
EXECUTE PROCEDURE [OwnerName.]ProcedureName
[(ParameterValue [,...])]
[IN DBEFileSetName]
- [Owner.]RuleName
is the name of the new rule. Two rules cannot have the same owner and
rule names.
The rule owner must be the same as the owner of the table the rule is
defined upon. The default owner name is the owner name of the table it is
being defined on. The usual default owner rules do not apply
here.
- StatementType
specifies which statements will cause the rule to fire for the given
table. StatementType must be one of the following:
Each statement type can be listed in the CREATE RULE statement
only once for a given rule. If ColumnNames are specified
for a StatementType of UPDATE, they must exist in
the table.
For UPDATE statements in which more than one column is specified,
any one of the column names listed here may be used in the UPDATE
for the rule to affect the statement. When you issue the UPDATE,
it is not necessary to specify all the ColumnNames in the
CREATE RULE statement. At most, 1023 column names may be specified
in this column name list.
- [Owner.]TableName
designates the table on which the rule is to operate. Rules cannot be
created on views.
- OldCorrelationName
specifies the correlation name to be used within the
FiringCondition and ParameterValue to
refer to the old values of the row (before it was changed by the
DELETE or UPDATE statement). The default
OldCorrelationName is OLD. If the
StatementType is INSERT, an
OldCorrelationName will refer to the new values of the
row, since no old values are available.
- NewCorrelationName
specifies the correlation name to be used within the
FiringCondition and ParameterValue to
refer to the new values of the row (after it was changed by the
INSERT or UPDATE statement). The default
NewCorrelationName is NEW. If the
StatementType is DELETE, a
NewCorrelationName will refer to old values of the row,
since no new values are available.
- FiringCondition
specifies a search condition the current row must meet once the rule's
statement type has matched before the rule can fire on that row. Refer to
the "Search Conditions" chapter for possible predicates.
The search condition must evaluate to TRUE to invoke the specified
procedure. The search condition cannot contain any subqueries, aggregate
functions, host variables, local variables, procedure parameters, dynamic
parameters, or the TID function.
- [Owner.]Procedure Name
specifies the procedure to invoke when a rule fires. The procedure
must exist when the rule is created.
- ParameterValue
specifies a value for a parameter in the procedure. The parameter
values must correspond in sequential order to the parameters defined for
the procedure.
ParameterValue has the following syntax:
{ NULL | Expression }
The Expression may include anything allowed within an SQL
expression except a subquery, aggregate function, host variable, TID
function, local variable, procedure parameter, dynamic parameter, or a
long column value. Refer to the "Expressions" chapter for the complete
syntax of expressions. In particular, column references
are allowed within the EXECUTE PROCEDURE clause of
the CREATE RULE statement. Column references may be of the form:
{ OldCorrelationName.ColumnName
NewCorrelationName.ColumnName
[[Owner.]TableName.]
ColumnName }
- DBEFileSetName
specifies the DBEFileSet in which sections associated with the rule
are to be stored. If not specified, the default SECTIONSPACE DBEFileSet
is used. (Refer to syntax for the SET DEFAULT DBEFILESET
statement.)
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.
If a CurrentFunction is used within the
FiringCondition or a ParameterValue,
it will be evaluated at the time of the statement that fires the
rule.
Any value returned by the procedure with a RETURN statement
is ignored by the rule and not returned to the statement firing the
rule.
An EXECUTE PROCEDURE call from within a rule is different
from one issued as a regular SQL statement. Within a rule, you cannot
specify host variables, local variables, procedure parameters, or
dynamic parameters as parameter values, since host variables are not
accessible from the rule. Also, the key word OUTPUT cannot be
specified, since a procedure called from a rule cannot return any
values. A rule does permit the specification of
columns within the procedure call, since in this context column
values are available to be passed to the procedure from the row the
rule is firing on.
The CREATE RULE statement invalidates sections that contain
dependencies upon the table the rule is defined upon. This is to
enable the rule to be included when those sections are
revalidated.
If a procedure specified in a CREATE RULE statement returns
multiple row result set(s), a warning is issued when the rule is
created. Note that no warning is issued when the procedure is invoked
by the rule.
If the IN DBEFileSetName clause is specified, but
the rule owner does not have SECTIONSPACE authority for the specified
DBEFileSet, a warning is issued and the default SECTIONSPACE
DBEFileSet is used instead. (Refer to syntax for the GRANT
statement and the SET DBEFILESET statement.)
The CREATE RULE statement requires you to have OWNER authority for the
table and OWNER or EXECUTE authority for the procedure, or to have
DBA authority. Once the rule is defined, users issuing statements
which cause the rule to fire need not have EXECUTE authority for
the procedure.
To specify a DBEFileSetName for a rule, the rule owner must
have SECTIONSPACE authority on the referenced DBEFileSet.
First, create a procedure to monitor operations on the Reports table:
CREATE PROCEDURE PurchDB.ReportMonitor (Name CHAR(20) NOT NULL,
Owner CHAR(20) NOT NULL, Type CHAR(10) NOT NULL) AS
BEGIN
INSERT INTO PurchDB.ReportMonitor
VALUES (:Type, CURRENT_DATETIME,
USER, :Name, :Owner);
RETURN ::sqlcode;
END
IN PurchDBFileSet;
Next, create three rules that invoke the procedure with parameters:
CREATE RULE PurchDB.InsertReport
AFTER INSERT TO PurchDB.Reports
EXECUTE PROCEDURE PurchDB.ReportMonitor (NEW.ReportName,
NEW.ReportOwner, 'INSERT')
IN PurchDBFileSet;
CREATE RULE PurchDB.DeleteReport
AFTER DELETE FROMPurchDB.Reports
EXECUTE PROCEDURE PurchDB.ReportMonitor (OLD.ReportName,
OLD.ReportOwner, 'DELETE')
IN PurchDBFileSet;
CREATE RULE PurchDB.UpdateReport
AFTER UPDATE TO PurchDB.Reports
EXECUTE PROCEDURE PurchDB.ReportMonitor (NEW.ReportName,
NEW.ReportOwner, 'UPDATE')
IN PurchDBFileSet;
|