Using 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

• 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.

Creating Rules

   CREATE RULE PurchDB.RemovePart
      AFTER DELETE FROM PurchDB.Parts
      WHERE SUBSTRING(PartNumber,1,4) < > 'XXXX'
      EXECUTE PROCEDURE PurchDB.ListDeletes (OLD.PartNumber);

   CREATE PROCEDURE PurchDB.ListDeletes (PartNumber CHAR(16) NOT NULL)
   AS BEGIN
      INSERT INTO PurchDB.Deletions
      VALUES (:PartNumber, CURRENT_DATETIME);
   END;

Techniques for Using Procedures with Rules

• Using Rule Chaining

• Using a Single Procedure

Using a Chained Set of Procedures and Rules

   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

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

  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 SupplyCursor;
     FETCH SupplyCursor INTO :VendPartNum;

     WHILE ::sqlerrd2 = 1 DO
        DELETE FROM PurchDB.OrderItems WHERE
          VendPartNumber = :VendPartNum;

        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;

   CREATE RULE PurchDB.RemovePart
   AFTER DELETE FROM PurchDB.Parts
   EXECUTE PROCEDURE PurchDB.RemovePart (OLD.PartNumber);

Error Handling in Procedures Invoked by Rules

Using RAISE ERROR in Procedures Invoked by Rules

   CREATE RULE PurchDB.CheckVendor
   AFTER DELETE FROM PurchDB.Vendors
   EXECUTE PROCEDURE PurchDB.DelVendor (OLD.VendorNumber);

   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;

  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=>

Enabling and Disabling Rules

   isql=> disable rules;

   isql=> enable rules;

Special Considerations for Procedures Invoked by Rules

• Transaction handling.

• Effects of rule chaining.

• Invalidation of sections.

• Changing session attributes.

• Performance considerations.

Transaction Handling in Rules

   if :Flag = 0 then
      commit work;
   endif;

Effects of Rule Chaining

• 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.

Invalidation of Sections

• 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

• SET CONSTRAINTS

• DISABLE RULES

• ENABLE RULES

• SET PRINTRULES

• SET USER TIMEOUT

Performance Considerations

Differences between Rules and 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.