ALLBASE/SQL Reference Manual (36216-90216)

Chapter 4 159
Constraints, Procedures, and Rules
Using Rules
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);