Reject Technique for Enforcing Referential Integrity
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.
Example: Enforce Referential Integrity Between an Employee and Manager
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.