|
|
|
A rule is a user-defined mechanism that invokes a database procedure whenever the database changes in a specified way, for example by insert, update, or delete.
A rule is a general-purpose mechanism that can be implemented for many purposes. For example, integrities can be implemented by rules. With integrities, violations are not specifically flagged or reported as errors. With rules, however, you can control exactly what happens when a violation occurs by defining in the database procedure the actions to take.
|
|
|
A rule is always associated with a database procedure that is executed when the rule is fired. Before creating a rule, you must create its corresponding database procedure, and you must have execute privileges for the database procedure invoked by the rule. For details, see the chapter "Ensuring Access Security."
|
|
|
You can create and work with rules using either VDBA or SQL statements.
In VDBA, rules are implemented using rule objects. Using the Rules branch for a particular table in the Database Object Manager window, you can:
For the detailed steps for performing these procedures, see the Procedures section of online help.
In SQL, you can accomplish these tasks using the create rule statement, drop rule statement, and the rules and norules options of the set statement. For more information, see the SQL Reference Guide.
|
|
|
After a rule object is created, the rule is stored with the table in the database and is applied continuously. Whenever the execution of a statement satisfies an existing rule condition, that rule is fired, meaning that the database procedure associated with the rule is executed. There is no need for application code to explicitly enforce the rule.
It is also possible for a statement in a rule-invoked database procedure to fire another rule. Rules can be nested in this manner up to a maximum level specified by the DBMS configuration parameter, rule_depth.
Any user who has the privilege to access the table through the operation specified by the rule has implicit permission to fire the rule and execute its associated database procedure. For information on privileges and how they are defined, see the chapter "Ensuring Access Security."
|
|
|
Rules can be defined to execute before or after the effect of the triggering statement is applied. AFTER rules are more common and are used to perform auditing operations, integrity checks, and other operations on the updated rows. BEFORE rules can be used to validate and replace values in an inserted or updated row before the row is stored in the database. Both types of rows can be used to inhibit the execution of the triggering statement if an error condition is encountered, although before rules can typically do so more efficiently.
|
|
|
Rules can be defined to execute the corresponding database procedure once for each row affected by the triggering statement or to execute the procedure once for the entire execution of the triggering statement. BEFORE rules must be defined as "for each row," but AFTER rules can optionally be defined as "for each statement." Statement level rules accumulate the procedure parameters for each triggering row as rows in a temporary table that is passed as the only parameter to the procedure. The procedure can then retrieve the parameter "rows" by selecting from the temporary table parameter. This approach can significantly reduce the overhead of rule execution.
|
|
|
For example, if you wanted to implement a rule equivalent to an integrity in which the condition was salary <= 50000, create the rule by filling in the VDBA Create Rule dialog as follows:
Here, "new" is a correlation name, and "new.salary" is a correlation reference. With any column name, you can specify whether you want to use its value before or after the update using the correlation name "old" or "new," respectively.
Note: Unlike an integrity check, which specifies a condition that cannot be violated, a rule specifies a where condition that must be met. Thus, the integrity condition (salary <= 50000), and the rule where condition (shown in the step above), are opposites.
The specified database procedure is executed and sent any specified parameters when the salary column has a value that is too big (that is, greater than $50,000). The procedure can be written to reject the operation, causing it to be rolled back; log the error, but allow the operation; or modify the value so that it is less than or equal to $50,000, and log that action.
|
|
|
The statement that fires a rule and the database procedure invoked by the rule are considered part of the same single query transaction. Consequently, the database procedure invoked by the rule is executed before the statement that fired the rule completes. Because of this, you cannot issue a commit or rollback statement in a database procedure invoked by a rule.
If the database procedure does not exist when the rule is invoked, or if an error occurs in the execution of a rule, the response is as if the statement firing the rule has experienced a fatal error. Any changes made to the database by the statement and any made by the fired rule are rolled back.
|
|
|
A referential integrity asserts a relationship between two tables such that the values in a column of one table must match the values in a column of the second table. Traditionally, the two tables have a parent-child relationship:
A primary key does not have to be referenced by a foreign key (that is, there can be a parent without a child). However, every foreign key must match a primary key. There cannot be a child without a parent (that is, an orphan)—this constitutes a referential integrity violation.
For example, for the parent table, create a rule to fire on an update or delete of the primary key (an insert simply creates a parent without a child, which is not an integrity violation). The database procedure can check for foreign keys that reference the primary key and enforce the referential integrity.
For example, for the child table, create a rule to fire on an update or insert of the foreign key. The database procedure checks to make sure there is a parent.
The advantage of using a rule (as opposed to a constraint) to enforce referential integrity is that the actions performed by a rule can be more complex than merely checking for the existence of a primary key in a parent table. For example, a rule can fire a procedure to create an appropriate parent record if one does not already exist.
There are a number of ways that a referential integrity violation can be handled. Three common techniques are to reject, nullify, or cascade the firing statement.
|
|
|
Rejecting a value that violates an integrity constraint rolls back the statement that fired the rule. The raise error statement performs this function, informing the application that the results from the statement firing the rule violated some specified condition or constraint. The response to a raise error statement is the same as if the statement that fired the rule experienced a fatal error—the firing statement is aborted and any changes to the database resulting from the statement and subsequent rule firing are rolled back.
|
|
|
For example, the following database procedure can be invoked by a rule to enforce referential integrity between an employee and the employee's manager and department. The code for the procedure, which has a procedure name of valid_mgr_dept, is shown as it is entered in the VDBA Create Procedure dialog:
Parameters
ename varchar(30),
mname varchar(30),
dname varchar(10)
Declare Section
msg varchar(80) not null;
check_val integer;
mgr_dept varchar(10);
Statements
/* Check to see if there is a matching manager */
select count(*) into :check_val from manager
where name = :mname and dept = :dname;
if check_val = 0 then
msg = 'Error 1: Manager "' +
:mname + '" not found in that dept.';
raise error 1 :msg;
return;
endif;
/* Check to be sure there is a matching dept */
select count(*) into :check_val
from dept where name = :dname;
if check_val = 0 then
msg = 'Error 2: Department "' +
:dname + '" not found.';
raise error 2 :msg;
return;
endif;
msg = 'Employee "' + ename + '" updated ' +
'(mgr = "' + mname + '", dept = "' + dname + '")';
message :msg;
insert into emplog values (:msg);
This procedure checks the manager table to make sure that the employee's manager manages the department to which the employee is assigned. It checks the department table to see that the department is valid. If any of these checks fail, an error is issued and the new employee is not inserted into the employee table. If the constraint is met, a message is displayed and a log record is inserted into a journal table.
After defining this database procedure, create a rule to invoke it after updates and inserts, and enter the following for the procedure parameters:
ename = new.name, mname = new.mgr, dname = new.dept
Note: Any value referring to a column name in a parameter list must be preceded by a correlation name. Using the correlation name "old" or "new," specify whether you want to use the column value before or after the operation, respectively.
|
|
|
Nullifying is a second course of action in response to a violation of a referential integrity constraint if a foreign key does not have a matching primary key. (Nullifying means that the columns in the records in violation of the constraint are made null, as opposed to deleting the records or returning an error that the constraint was violated.)
You are not restricted to nullifying the foreign key. You can modify the value to another defined value. Because null is not a value, it traditionally does not participate in the referential integrity relationship. Thus, a child row with a null foreign key value is not generally considered an orphan. However, rules provide you with the facilities to do such things as simulate matches on nulls.
For example, the following database procedure, nullify_children, can be invoked by a rule, when a parent row is deleted, to nullify all child entries belonging to that parent:
Parameters
me varchar(10)
Declare Section
msg varchar(80) not null;
Statements
msg = 'Nullifying child(ren) of "' + :me + '"';
message :msg;
update person set parent = NULL where parent = :me;
if iirowcount > 0 then
msg = 'Nullified ' + varchar(:iirowcount) +
' child(ren) from "' + :me + '"';
else
msg = 'No children nullified from "' + :me + '"';
endif;
message :msg;
After defining this database procedure, create a rule to invoke it after deletes, and enter the following for the procedure parameters:
me = old.name
|
|
|
Cascading is the third available option in response to a violation of a referential integrity constraint. (Cascading means that the original update applies to other records that violate the constraint.) If the statement that violates the constraint is:
The database procedure shown in this example, delete_children, can be used to implement a cascading delete rule. The procedure can be invoked by a rule, when a parent row is deleted, to delete all child entries belonging to that parent:
Parameters
me varchar(10)
Declare Section
msg varchar(80) not null;
Statements
msg = 'Deleting child(ren) from "' + :me + '"';
message :msg;
delete from person where parent = :me;
if iirowcount > 0 then
msg = 'Deleted ' + varchar(:iirowcount) +
' child(ren) from "' + :me + '"';
else
msg = 'No children deleted from "' + :me + '"';
endif;
message :msg;
After defining this database procedure, create a rule to invoke it after deletes, and enter the following for the procedure parameters:
me = old.name
When the rule is fired after the initial delete statement, it executes the delete_children database procedure, which deletes all children whose parent is the current person. Each delete statement in the delete_children procedure, in turn, also fires the delete rule, until a particular person has no descendants. The message statements that are executed before and after a row is deleted demonstrate the order in which the tree is traversed.
Note: In this example, the person table is self-referencing, and functions like a self-join. Referential integrity does not require two separate tables. Here the primary key is name and the foreign key is parent, both of which are in the person table.
|
|
|
To set up tables that maintain data calculated from other tables, use views on normalized tables. For functional, performance, or data distribution reasons, the derived data must be maintained in another table or even in a specific column of the same table.
A general integrity is any integrity check that is not a referential integrity. General integrities can be used, for instance, to describe the relationship between the original data and the derived data, and a rule can be used to enforce the described relationship.
For example, consider two tables, employee and department. The employee table contains employee information, including the name of the department in which each employee works. The department table includes the number of employees in each department. Given these tables, a useful general constraint is that the number of employees listed for a row in the department table must match the number of employees in the employee table who work in that department.
This constraint can be enforced using rules to correctly update a row in the department table whenever an employee is hired, leaves, or changes departments. For example, if you create a database procedure that updates the department table whenever a new employee is hired, define a rule to invoke it after an insert, passing the department number as a parameter.
|
|
|
General-purpose rules are those rules that do not fall in the category of either referential or general integrity constraints.
|
|
|
You can use general purpose rules to apply external resource controls.
For example, if you have a table of items in stock, define a rule that fires after an update to the in_stock column. The following where clause causes the rule to fire if the number of items in stock is reduced to less than a minimum value of 100:
items.in_stock < 100
The rule executes a database procedure that reorders the item responsible for firing the rule, passing as parameters an item identifier and the number of items in stock. For example:
id = items.id, items_left = items.in_stock
|
|
|
A rule can be created to extend the permission system by ensuring that unauthorized users cannot modify certain classified rows in the opcodes table. The rule, which must be fired after inserts and deletes, is defined with the following where clause:
opcodes.scope = 'share' and user != 'system'
The database procedure invoked by this rule can issue an error (using the raise error statement, which rejects the statement that fired the rule) and log the operation with the user name into a local log table for later review (the next example demonstrates logging).
|
|
|
This example tracks changes to personnel numbers. When an employee is removed, an entry is made into the manager table, which in turn causes an entry to be made into the director table. Even if an entry is made directly into the manager table, the director table is notified.
To implement this, two database procedures need to be defined. The first, manager_emp_track, updates the manager table by reducing the number of employees for a manager, and inserts an entry into a separate table, mgrlog, to log which employee was deleted for the manager:
Parameters
ename varchar(30),
mname varchar(30)
Statements
update manager set employees = employees – 1
where name = :mname;
insert into mgrlog values ('Manager: ' +
:mname + ', Deleted employee: ' + :ename);
The second, director_emp_track, updates the director table by reducing the number of employees for a director:
Parameters
dname varchar(30)
Statements
update director set employees = employees - 1
where name = :dname;
Two rules also need to be defined. The first one, defined for the employee table, executes manager_emp_track after a delete operation, passing the following parameters:
ename = old.name, mname = old.manager
The second rule, defined for the manager table, executes director_emp_track after an update operation on the employees' column that reduces the number of employees by one. To implement the rule, the following where clause must be defined:
old.employees - 1 = new.employees
Director_emp_track must be defined as the database procedure with the following parameters:
dname = old.director
This rule is fired by the manager_emp_track procedure, because it reduces the number of employees by one, but it is also fired if the manager table is updated directly.
|
|
|
If you use the copy statement on a table with rules defined, the table's rules are completely ignored. Table integrities are ignored in this same manner. How to effectively apply rules in this situation is described in The Copy Statement and Enforcing Integrities.
|
|
|
By default, rules are enabled. The set norules statement enables you to turn off rules when necessary (for example, when using a utility that loads or unloads a database in which tables can be modified from scripts and files prior to their processing by applications).
To issue this statement, you must be the DBA of the database to which the session is connected.
The set norules statement disables any rules that apply to statements executed during the session or to the tables affected by the statements. Existing rules as well as rules created during the session are disabled.
To re-enable rules, issue the set rules statement.
Warning! After you issue the set norules statement, the DBMS does not enforce check and referential constraints on tables, nor does it enforce the check option for views.
For more information on using set [no] rules, see the entry for the set statement in the SQL Reference Guide.