Rules
A rule invokes a specified database procedure when a specified change to the database is detected. When the DBMS Server detects the change, the rule is fired and the database procedure associated with the rule is executed. Rules can be fired by:
• Any INSERT, UPDATE, or DELETE on a specified table (including a cursor update or delete)
• An update that changes one or more columns in a table
• A change that results in a specified condition (expressed as a qualification)
Note: Rules can also be fired by the QUEL statements APPEND, DELETE, and REPLACE.
Rules are created with the CREATE RULE statement and dropped with the DROP RULE statement. Dropping the procedure invoked by a rule does not drop the rule. For more information about creating and dropping rules, see Create Rule and Drop Rule in the chapter "SQL Statements."
Use rules to enforce referential and general integrity constraints, and for general purposes such as tracking all changes to particular tables or extending the permission system. For a detailed discussion of the use of rules to enforce referential integrity and data security, see the Database Administrator Guide.
The statement that fires the rule can originate in an application, a database procedure, or an Ingres tool such as QBF.
The statement that fires a rule and the database procedure invoked by the rule are treated as part of the same statement. The database procedure is executed before the statement that fired the rule completes. For this reason, a COMMIT or ROLLBACK statement cannot be issued in a database procedure invoked by a rule. If a statement fires more than one rule, the order in which the database procedures are executed is undefined. To trace the execution of rules, use the SET PRINTRULES statement.
For an UPDATE or DELETE statement, the DBMS Server executes a rule once for each row of the table that meets the firing condition of the rule. The rule is actually executed when the row is updated or deleted and not after the statement has completed. Thus, an UPDATE statement that ranges over a set of rows and that has a rule applied to it fires the rule each time a row is modified, at the time the row is modified. This style of execution is called instance-oriented.
Rules can be fired as the result of a statement issued from an invoked database procedure. Rules can be forward-chained, or nested, in this manner to a predefined number of levels. If this depth is exceeded, the DBMS Server issues an error and the statement is rolled back. By default, 20 levels of nesting can be defined. To change this value, set the RULE_DEPTH server parameter. Like a non-nested rule, when a nested rule fires, its database procedure is executed before the statement that fired it is completed.
Before creating or invoking a rule, the associated database procedure must exist. If it does not exist when the rule is created, the DBMS Server issues an error. If it does not exist when the rule is invoked, the DBMS Server issues an error and aborts the statement that attempted to fire the rule.
If an error occurs in the execution of a rule, the DBMS Server responds as if the statement firing the rule has experienced a fatal error and rolls back any changes made to the database by the statement and any made by the fired rule. An error also occurs when the RAISE ERROR statement is issued.
To create a rule against a table, you must own the table. In addition, you must either own the invoked database procedure or have execution privileges for that procedure.
After a rule is created against a table, any user who has permission to access the table using the operation specified by the rule has implicit permission to fire the rule and execute its associated database procedure.
Note: The DBA for a database can disable the firing of rules within that database during a session using the SET [NO]RULES statement. This option is provided as an aid to rule development and database maintenance tasks.
Important! If rules are disabled using the SET NORULES statement, the DBMS Server does not enforce table constraints or check options for views.
Rules are not fired by the COPY and MODIFY statements.