ALLBASE/SQL Reference Manual (36216-90216)

160 Chapter4
Constraints, Procedures, and Rules
Using Rules
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;