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:

Delete from Parts table.
Fire rule RemovePart.
Invoke procedure RemovePart.
Delete from Inventory table.
Delete from SupplyPrice table.
Fire rule RemoveVendPart.
Invoke procedure RemoveVendPart.
Delete from OrderItems table.
Delete from SupplyBatches table.
Fire rule RemoveBatchStamp.
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.