Step 6. Complete Referential Integrity

REP007 - File Validation Rules/Triggers

In this step, you will complete the referential integrity checks. You will add a rule that checks if any employees exist for a specific department before allowing the department to be deleted from the Department File. This rule will prevent an employee from losing its parent department.

1.  Open file iiiDepartments in the Editor. 

2.  Select the Rules and Triggers tab.

3.  Select the key field iiiDeptCode field in the list of rules.

4.  In the Rules and Triggers group on the Home ribbon, expand the Add menu to select Add Lookup Check to add the rule to the iiiDeptCode field.

5.  In the Details tab, create the rule as follows:

Description Cannot delete - has Employee
Sequence 2
File Name iiiEmpByDeptView
When Inserting Never apply rule
When Updating Never apply rule
When Deleting Always apply rule (DLT)

 

   Use the ellipsis button for File Name to use the Find dialog  :

 

     Note that the rule is only enforced when deleting a record. Your Details tab should now look like this:

6.  In the Actions section, define the File Lookup Rule to set the field in error if the department exists in the Employee file:

If a "key match" is found in target file

Set field in error (ERROR)

If a "key match" is NOT found in target file

Evaluate next rule (NEXT)
Message file

(delete the entry in this field)

Message Number

(delete the entry in this field)

Message text Cannot delete if used in Employee file

 

     Your Details tab should now look like this:

7.  Recompile the file iiiDepartments.

     Check the file compile completed successfully.

8.  Execute your Department Maintenance form iiiMainDepartment

a.  Try to delete department ADM. You will not be able to delete this record.

b.  Add a new department XYZ. Delete this department. The delete will be allowed because no employees have been defined for this department.

9.  Close the form.

10.  Close the file definition in the editor.