ALLBASE/SQL Reference Manual (36216-90216)

164 Chapter4
Constraints, Procedures, and Rules
Using Rules
Then the procedure can be coded with IF statements like the following:
if :Flag = 0 then
commit work;
endif;
The flag check ensures that the rule will not execute statements that would cause it to
generate an error when the procedure is invoked by a rule, while user calls can commit or
roll back changes automatically.
Effects of Rule Chaining
Procedures invoked by rules can include data manipulation statements that invoke rules
that trigger the execution of other procedures. Excessive chaining of rules in this fashion
uses additional system resources. When the chain length exceeds 20, an error occurs,
which causes the user's statement to be undone. To avoid problems, be sure to trace the
dependencies of statements within procedures invoked by rules so as to:
• avoid an endless loop of rule chaining.
• avoid exceeding a rule depth greater than the maximum of 20.
• control and maintain the rule system with minimal complexity.
To assist in tracing, the DBA can use the SET PRINTRULES ON statement to display the
names of rules being fired.
The rule developer should also determine if multiple rules will apply to the same data
manipulation statement. An analysis of the rule type and WHERE conditions can be done
to see whether any rules overlap in statement type on a given table, and whether their
conditions are mutually exclusive or not. The rules are checked for each row an INSERT,
DELETE, or UPDATE statement affects. If multiple rules can affect a single row, the order of
their execution is not guaranteed to be fixed if the section is ever revalidated. To avoid
potential problems, it is best to ensure that rules affecting the same statement have
mutually exclusive WHERE conditions or that the order of execution of the procedures
they invoke is unimportant.
Invalidation of Sections
Procedures can include data definition statements that affect the execution of procedures
and rules by invalidating sections. Use care when issuing the following statements inside
procedures:
• DROP PROCEDURE. If a rule depends on the procedure, all sections checking that rule
will be invalidated by the DROP PROCEDURE statement, and will fail to be revalidated.
• CREATE RULE and DROP RULE. Because rule enforcement is checked during the
lifetime of the rule, CREATE RULE and DROP RULE should be used with care. If a rule
that is currently among those checked for a statement is dropped within a procedure
invoked by a rule on behalf of that statement, the statement will be invalidated while it
is still being executed. In this situation, execution will halt, an error will occur, and the
statement will be undone.
• Any data definition. Within a procedure invoked by a rule, if any DDL is performed
which invalidates a statement currently being executed (either the user's statement, or