Triggers and their relationship to referential integrity

 

A physical file can have both triggers and referential constraints associated with it. The running order among trigger actions and referential constraints depends on the constraints and triggers that are associated with the file.

In some cases, the system evaluates referential constraints before the system calls an after trigger program. This is the case with constraints that specify the RESTRICT rule.

In some cases, all statements in the trigger program, including nested trigger programs, run before the constraint is applied. This is true for NO ACTION, CASCADE, SET NULL, and SET DEFAULT referential constraint rules. When you specify these rules, the system evaluates the file's constraints based on the nested results of trigger programs. For example, an application inserts employee records into an EMP file that has a constraint and trigger:

• The referential constraint specifies that the department number for an inserted employee record to the EMP file must exist in the DEPT file.

• Whenever an insert to the EMP file occurs, the trigger program checks if the department number exists in the DEPT file. The trigger program then adds the number if it does not exist.

When the insertion to the EMP file occurs, the system calls the trigger program first. If the department number does not exist in the DEPT file, the trigger program inserts the new department number into the DEPT file. Then the system evaluates the referential constraint. In this case, the insertion is successful because the department number exists in the DEPT file.

There are some restrictions when both a trigger and referential constraint are defined for the same physical file:

• If a delete trigger associates with a physical file, that file must not be a dependent file in a referential constraint with a delete rule of CASCADE.

• If an update trigger associates with a physical file, no field in this physical file can be a foreign key in a referential constraint with a delete rule of SET NULL or SET DEFAULT.

If failure occurs during either a trigger program or referential constraint validation, all trigger programs associated with the change operation roll back if all the files run under the same commitment definition. The referential constraints are guaranteed when all files in the trigger program and the referential integrity network run under the same commitment definition. If you open the files without commitment control or in a mixed scenario, undesired results might occur.

You can use triggers to enforce referential constraints and business rules. For example, you can use triggers to simulate the update cascade constraints on a physical file. However, you would not have the same functional capabilities as provided by the constraints that the system referential integrity functions define. You might lose the following referential integrity advantages if you define them with triggers:

• Dependent files might contain rows that violate one or more referential constraints that put the constraint into check pending but still allow file operations.

• The ability to inform users when the system places a constraint in check pending.

• When an application runs under COMMIT(*NONE) and an error occurs during a cascaded delete, the database rolls back all changes.

• While saving a file that is associated with a constraint, the database network saves all dependent files in the same library.

 

Parent topic:

Triggering automatic events in your database