ALLBASE/SQL Reference Manual (36216-90216)

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