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

CREATE RULE [Owner.] RuleName AFTER StatementType [,...] { ON 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 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.)

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;