HP 3000 Manuals HP 3000 Manuals
CREATE RULE
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.
Scope
ISQL or Application Programs
SQL Syntax
{ON }
CREATE RULE [Owner.]RuleName AFTER StatementType [,...] {OF }
{FROM}
{INTO}
[Owner].TableName [REFERENCING {OLD AS OldCorrelationName}[...]]
[ {NEW AS NewCorrelationName} ]
[WHERE FiringCondition] EXECUTE PROCEDURE [OwnerName.]ProcedureName
[(ParameterValue [,...])] [IN DBEFileSetName]
Parameters
[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:
* INSERT
* UPDATE [(ColumnName [,...])]
* DELETE
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 specifies the procedure to invoke when a rule
Name 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.)
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.
* 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.)
Authorization
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.
Example
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;