ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 349
SQL Statements A - D
CREATE RULE
• 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;