ALLBASE/SQL Reference Manual (36216-90216)

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