HP 3000 Manuals HP 3000 Manuals
Using Rules
Rules allow you to tie procedures to data manipulation statements. Rules
are more flexible than simple integrity constraints, enabling you to
incorporate complex business rules into the structure of a DBEnvironment
with minimal application programming. The following sections describe
the use of rules:
* Understanding Rules
* Creating Rules
* Techniques for Using Procedures with Rules
* Error Handling in Procedures Invoked by Rules
* Using RAISE ERROR in Procedures Invoked by Rules
* Enabling and Disabling Rules
* Special Considerations for Procedures Invoked by Rules
* Differences between Rules and Integrity Constraints
Understanding Rules
Rules allow you to define generalized constraints by invoking procedures
whenever specified operations are performed on a table. The rule fires,
that is, invokes a procedure, each time the specified operation (such as
INSERT, UPDATE, or DELETE) is performed and the rule's search condition
is satisfied.
Rules tie procedures to particular kinds of data manipulation statements
on a table. This permits data processing to be carried out by the
DBEnvironment itself. The effect is less application coding and more
efficient use of resources. This is especially important for networked
systems.
Rules will fire under the following conditions:
* The rule's statement types must include the statement type of the
current statement. Statement types are INSERT, DELETE, and
UPDATE. (You can have more than one statement type per rule.)
* If the rule's statement type includes UPDATE, and if the
StatementType clause includes a list of columns in the table, and
if the current statement is an update, it must be on at least one
of the listed columns of that table.
* The rule's search condition must evaluate to TRUE for the current
row of the current statement.
A rule fires once for each row operated on by the current statement that
satisfies the rule's search condition.
Creating Rules
A rule is defined in a CREATE RULE statement, which identifies a table,
types of data manipulation statements, a firing condition, and a
procedure to be executed whenever the condition evaluates to TRUE and the
data manipulation statement is of the right type.
The following is a simple example of a rule tied to deletions from the
Parts table:
CREATE RULE PurchDB.RemovePart
AFTER DELETE FROM PurchDB.Parts
WHERE SUBSTRING(PartNumber,1,4) < > 'XXXX'
EXECUTE PROCEDURE PurchDB.ListDeletes (OLD.PartNumber);
The table on which the rule is defined is PurchDB.Parts. The statement
type required to trigger the procedure is the DELETE operation. The
search condition that must be satisfied in addition to the statement type
of DELETE is that the first four characters in PartNumber must not be
"XXXX." The procedure to be executed is PurchDB.ListDeletes, shown in the
following:
CREATE PROCEDURE PurchDB.ListDeletes (PartNumber CHAR(16) NOT NULL) AS
BEGIN
INSERT INTO PurchDB.Deletions
VALUES (:PartNumber, CURRENT_DATETIME);
END;
When a row containing a part number that does not start with XXXX is
deleted from the Parts table, its number is inserted along with the
current date and time, in the PurchDB.Deletions table.
Techniques for Using Procedures with Rules
One common use of the rule-and-procedure combination is to enforce
integrity within a DBEnvironment. This can be done in different ways,
depending on your needs. The following sections contrast two approaches
to integrity enforcement:
* Using Rule Chaining
* Using a Single Procedure
Using a Chained Set of Procedures and Rules.
The following example uses a chained set of procedures and rules to
remove all references to a part number once it has been deleted from the
database. In this case a rule fires a procedure, which causes another
delete, which causes another rule to invoke an additional procedure, and
so on.
CREATE PROCEDURE PurchDB.RemovePart (PartNum CHAR(16) NOT NULL)
AS BEGIN
DELETE FROM PurchDB.Inventory WHERE PartNumber = :PartNum;
DELETE FROM PurchDB.SupplyPrice WHERE PartNumber = :PartNum;
END;
CREATE RULE PurchDB.RemovePart
AFTER DELETE FROM PurchDB.Parts
EXECUTE PROCEDURE PurchDB.RemovePart (OLD.PartNumber);
CREATE PROCEDURE PurchDB.RemoveVendPart (VendPartNum CHAR(16) NOT NULL)
AS BEGIN
DELETE FROM PurchDB.OrderItems WHERE VendPartNumber = :VendPartNum;
DELETE FROM ManufDB.SupplyBatches WHERE VendPartNumber = :VendPartNum;
END;
CREATE RULE PurchDB.RemoveVendPart
AFTER DELETE FROM PurchDB.SupplyPrice
EXECUTE PROCEDURE PurchDB.RemoveVendPart (OLD.VendPartNumber);
CREATE PROCEDURE ManufDB.RemoveBatchStamp (BatchStamp DATETIME NOT NULL)
AS BEGIN
DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp;
END;
CREATE RULE ManufDB.RemoveBatchStamp
AFTER DELETE FROM ManufDB.SupplyBatches
EXECUTE PROCEDURE ManufDB.RemoveBatchStamp (OLD.BatchStamp);
Executing the Chained Set of Procedures and Rules.
Whenever a user performs a DELETE operation on PurchDB.Parts, the
procedures and rules are executed on each row of each table for the
identified part number in the following order:
1. Delete from Parts table.
2. Fire rule RemovePart.
3. Invoke procedure RemovePart.
4. Delete from Inventory table.
5. Delete from SupplyPrice table.
6. Fire rule RemoveVendPart.
7. Invoke procedure RemoveVendPart.
8. Delete from OrderItems table.
9. Delete from SupplyBatches table.
10. Fire rule RemoveBatchStamp.
11. Delete from TestData table.
Using a Single Procedure with Cursors.
The following example uses a single rule and one procedure to remove all
references to a part number once it has been deleted from the database.
In this case, a single procedure RemovePart determines which rows need to
be deleted in the other tables once a part number is deleted from the
Parts table. Since this method only uses one rule and one procedure, it
would be effective only when a DELETE is done from the Parts table.
Deletions of part numbers from other tables would not trigger any rules
at all.
The single procedure uses two cursors to scan the PurchDB.SupplyPrice and
ManufDB.SupplyBatches tables for entries that correspond to a deleted
part number. The procedure then performs deletions of qualifying rows in
PurchDB.OrderItems and ManufDB.TestData.
CREATE PROCEDURE PurchDB.RemovePart(PartNum CHAR(16) NOT NULL)
AS BEGIN
DECLARE VendPartNum CHAR(16) NOT NULL;
DECLARE BatchStamp DATETIME NOT NULL;
DECLARE SupplyCursor CURSOR FOR
SELECT VendPartNumber FROM PurchDB.SupplyPrice
WHERE PartNumber = :PartNum;
DECLARE BatchCursor CURSOR FOR
SELECT BatchStamp FROM ManufDB.SupplyBatches
WHERE VendPartNumber = :VendPartNum;
DELETE FROM PurchDB.Inventory WHERE PartNumber = :PartNum;
Open the first cursor:
OPEN SupplyCursor;
FETCH SupplyCursor INTO :VendPartNum;
WHILE ::sqlerrd2 = 1 DO
DELETE FROM PurchDB.OrderItems WHERE VendPartNumber = :VendPartNum;
Open the second cursor:
OPEN BatchCursor;
FETCH BatchCursor INTO :BatchStamp;
WHILE ::sqlerrd2 = 1 DO
DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp;
FETCH BatchCursor INTO :BatchStamp;
ENDWHILE;
CLOSE BatchCursor;
DELETE FROM ManufDB.SupplyBatches WHERE VendPartNumber = :VendPartNum;
FETCH SupplyCursor INTO :VendPartNum;
ENDWHILE;
CLOSE SupplyCursor;
DELETE FROM PurchDB.SupplyPrice WHERE PartNumber = :PartNum;
END;
The single rule that invokes the above procedure is as follows:
CREATE RULE PurchDB.RemovePart
AFTER DELETE FROM PurchDB.Parts
EXECUTE PROCEDURE PurchDB.RemovePart (OLD.PartNumber);
Error Handling in Procedures Invoked by Rules
When invoked by a rule, a procedure is executed inside the execution of a
data manipulation statement. Therefore, if the procedure encounters an
error, the effect of the procedure and the effect of the data
manipulation statement as a whole are undone. Statements that may fire
rules always execute with statement atomicity, regardless of the current
general error checking level set by the SET DML ATOMICITY statement.
Inside procedures invoked by rules, SQL errors have the usual effect of
issuing messages, halting execution of the current statement, rolling
back a transaction, or ending a connection. In addition, even if the
error does not result in rolling back a transaction or losing a
connection, it results in the undoing of the effects of all procedures
invoked in a chain by the current statement, and it results in the
undoing of the effects of all rules triggered by the current statement.
Thus the entire execution of the statement is undone.
Using RAISE ERROR in Procedures Invoked by Rules
Within a procedure which is triggered by a rule, the RAISE ERROR
statement can be used to generate an error, which causes an immediate
return and undoes the statement that triggered the rule. The text of the
RAISE ERROR message can provide useful information to the user such as
the procedure name, the exact reason for the error, the location in the
procedure, or the name of the rule that invoked the procedure (if the
procedure is only fired by one rule).
Suppose the following rule executes whenever a user attempts to delete a
row in the Vendors table:
CREATE RULE PurchDB.CheckVendor
AFTER DELETE FROM PurchDB.Vendors
EXECUTE PROCEDURE PurchDB.DelVendor (OLD.VendorNumber);
The procedure PurchDB.DelVendor checks for the existence of the use of a
vendor number elsewhere in the database, and if it finds that the number
is being used, it rolls back the delete on the Vendors table. The
procedure is coded as follows:
CREATE PROCEDURE PurchDB.DelVendor (VendorNumber INTEGER NOT NULL) AS
BEGIN
DECLARE rows INTEGER NOT NULL;
SELECT COUNT(*) INTO :rows FROM PurchDB.Orders
WHERE VendorNumber = :VendorNumber;
IF :rows <> 0 THEN
RAISE ERROR 1 MESSAGE 'Vendor number exists in the "Orders" table.';
ENDIF;
SELECT COUNT(*) INTO :rows FROM PurchDB.SupplyPrice
WHERE VendorNumber = :VendorNumber;
IF :rows <> 0 THEN
RAISE ERROR 1 MESSAGE 'Vendor number exists in "SupplyPrice" table.';
ENDIF;
END;
PurchDB.DelVendor checks for the existence of the use of a vendor number
in two tables: PurchDB.Orders and PurchDB.SupplyPrice. If it retrieves
any rows containing the vendor number, it returns an error code and a
string of text to the caller by means of the RAISE ERROR statement.
The following shows the effect of the rule and procedure when you attempt
to delete a row from the Vendors table in ISQL:
isql=> DELETE FROM purchdb.vendors WHERE vendornumber = 9006;
Vendor number exists in the "Orders" table.
Error occurred executing procedure PURCHDB.DELVENDOR statement 3.
(DBERR 2235)
INSERT/UPDATE/DELETE statement had no effect due to execution errors.
(DBERR 2292)
Number of rows processed is 0
isql=>
The DELETE statement triggers the rule, which executes the procedure
PurchDB.DelVendor. If the vendor number that is to be deleted is not
found in either of the two tables, sqlcode is 0, and no messages are
displayed.
When a procedure is called through the use of a rule, the procedure exits
as soon as an error occurs. This can be either an ordinary SQL error
(but not a warning), or a user-defined error produced with the RAISE
ERROR statement. After an error return, the statement that fired the
rule is undone, and the operation of all other rules fired by the
statement is also undone.
In application programs, you use SQLEXPLAIN to retrieve the messages
generated by RAISE ERROR and other SQL statements.
Enabling and Disabling Rules
Rule processing takes place by default in the DBEnvironment. However,
the DBA can use the following statement to disable the operation of rules
in the current session:
isql=> disable rules;
This statement, which is useful in debugging, should be employed only
with great care, since it can affect the integrity of the database, if
rules are being used to control data integrity. To restore the operation
of rules in the session, use the following statement:
isql=> enable rules;
Rules are not fired retroactively when the ENABLE RULES statement is
issued after the DISABLE RULES statement has been issued.
Special Considerations for Procedures Invoked by Rules
Procedures operate somewhat differently when invoked by rules than when
invoked directly by a user. The differences are most pronounced in
several areas:
* Transaction handling.
* Effects of rule chaining.
* Invalidation of sections.
* Changing session attributes.
* Performance considerations.
Transaction Handling in Rules.
Since rules are fired by data manipulation statements that are already
being executed, a transaction is always active when a rule invokes a
procedure. Therefore, BEGIN WORK and BEGIN ARCHIVE statements will
result in errors in a procedure invoked by a rule. The error will cause
the rule to fail and the user's statement to be undone.
COMMIT WORK, COMMIT ARCHIVE, ROLLBACK WORK, ROLLBACK ARCHIVE, SAVEPOINT,
and ROLLBACK TO SAVEPOINT statements will generate errors when
encountered in procedures triggered by rules. The error causes the
user's statement and all subsequent rule-driven statements to be undone.
If you wish to include COMMIT WORK, COMMIT ARCHIVE, ROLLBACK WORK,
ROLLBACK ARCHIVE, SAVEPOINT, or ROLLBACK TO SAVEPOINT statements in the
procedure, because the procedure will be executed by users directly as
well as by rules, you should include these statements within a condition
that will only be true for non-rule invocation. To do this, add a flag
parameter to the procedure. Have users invoking the procedure pass in a
fixed value (such as 0), and have rules invoking the procedure pass in a
different value (such as 1). 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 a statement within an
invoked procedure which chained another rule), an error will
occur, and the user's statement will be undone.
Changing Session Attributes.
Procedures should avoid the following statements, which change the
attributes of transactions or sessions:
* SET CONSTRAINTS
* DISABLE RULES
* ENABLE RULES
* SET PRINTRULES
* SET USER TIMEOUT
If you include one of these statements in a procedure invoked by a rule,
consider its effect carefully. If any of these statements is executed by
a procedure invoked by a rule, and it causes the setting of the attribute
to change, then the user's statement will execute partly in the original
mode and partly in the altered mode. In the event of rule chaining,
attributes might change several times. If a statement that invokes a
procedure is undone, any settings modified by the procedure are restored
to their values prior to the issuing of the statement.
The SET CONSTRAINTS statement will change the application of check
constraints as of the next statement in the procedure, and this change
will affect the remainder of the set of rows defined by the triggering
statement. The SET CONSTRAINTS statement will change the application of
unique and referential constraints as of the user's next statement--that
is, the statement following the one that invoked the procedure through a
rule.
The DISABLE RULES statement will have no effect on the firing of the
rules on their respective current rows. It will only affect rows not yet
checked and rules not yet fired. DISABLE RULES can be used to ensure
that the rule depth of 20 is not exceeded, if the chain of rule
dependencies is understood well enough for the appropriate placement of
this statement.
SET PRINTRULES ON and SET PRINTRULES OFF affect the printing of rule
names of rules not yet fired, or of rows not yet checked.
Performance Considerations.
The placement of conditions on execution of statements within the firing
of a rule should be examined carefully. Firing conditions placed in the
WHERE clause can avoid the overhead of loading and invoking the
procedure, since the WHERE condition is checked before the procedure is
invoked. Thus, it might be better to develop several rules with separate
conditions and procedures with well-defined actions rather than a single
rule with no condition and a single procedure that makes checks before
deciding what steps to carry out. To determine the best design for your
needs, weigh the overhead of frequent loading and executing of a
procedure against the overhead of maintaining several procedures and
rules.
Differences between Rules and Integrity Constraints
Rules are similar to integrity constraints in that when a rule is
created, all existing INSERT, UPDATE, and DELETE statements will be
affected by the rule (if the statement type is appropriate to the rule).
Rules are viewed as changes to the table definition, and so all existing
sections depending on the table are invalidated when a rule is created.
When these sections are next revalidated, the rule definition is picked
up and compared to the section; appropriate rules are then included in
the revalidated section for checking at statement execution time.
The following are some of the most important ways in which rules differ
from integrity constraints:
* Rules are entirely reactive. They are not fired at CREATE RULE
time against the existing rows in the table. Moreover, after
DISABLE RULES, no record is kept of rows the rule would have fired
on; so, when the ENABLE RULES statement is next issued, the rule
is not fired retroactively. Integrity constraints, on the other
hand, are always checked when an ALTER TABLE statement is issued
with the ADD CONSTRAINT clause, and when SET CONSTRAINTS IMMEDIATE
is executed.
* Rules only fire on the statement types they are defined to fire
on, whereas integrity constraints will be checked on all data
change operations.
* Rules do not use index structures to enforce the constraints they
define; some integrity constraints build special indexes.
* The only side effect of the integrity constraint is an error,
while a rule can have many different side effects depending on the
actions of the procedure it invokes.
* In addition to providing a general way of implementing
constraints, rules can be used to define more abstract tasks such
as logging the changes made to a table or enforcing stricter
security measures developed by the database designer. Rules are
most useful in defining complex relationships that cannot be
modeled with existing check, unique, or referential constraints.