ALLBASE/SQL Reference Manual (36216-90216)

158 Chapter4
Constraints, Procedures, and Rules
Using Rules
Understanding Rules
Rules allow you to define generalized constraints by invoking procedures whenever
specified operations are performed on a table. The rule fires, that is, invokes a procedure,
each time the specified operation (such as INSERT, UPDATE, or DELETE) is performed and
the rule's search condition is satisfied.
Rules tie procedures to particular kinds of data manipulation statements on a table. This
permits data processing to be carried out by the DBEnvironment itself. The effect is less
application coding and more efficient use of resources. This is especially important for
networked systems.
Rules will fire under the following conditions:
• 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.
A rule fires once for each row operated on by the current statement that satisfies the rule's
search condition.
Creating Rules
A rule is defined in a CREATE RULE statement, which identifies a table, types of data
manipulation statements, a firing condition, and a procedure to be executed whenever the
condition evaluates to TRUE and the data manipulation statement is of the right type.
The following is a simple example of a rule tied to deletions from the Parts table:
CREATE RULE PurchDB.RemovePart
AFTER DELETE FROM PurchDB.Parts
WHERE SUBSTRING(PartNumber,1,4) < > 'XXXX'
EXECUTE PROCEDURE PurchDB.ListDeletes (OLD.PartNumber);
The table on which the rule is defined is PurchDB.Parts. The statement type required to
trigger the procedure is the DELETE operation. The search condition that must be satisfied
in addition to the statement type of DELETE is that the first four characters in PartNumber
must not be “XXXX.” The procedure to be executed is PurchDB.ListDeletes, shown in the
following:
CREATE PROCEDURE PurchDB.ListDeletes (PartNumber CHAR(16) NOT NULL) AS
BEGIN
INSERT INTO PurchDB.Deletions
VALUES (:PartNumber, CURRENT_DATETIME);
END;
When a row containing a part number that does not start with XXXX is deleted from the
Parts table, its number is inserted along with the current date and time, in the
PurchDB.Deletions table.