12.19.6 Referential constraints and triggers

Organization of this subsection
(1) Triggers for referential constraint actions
(2) Relationship between referential constraints and user-defined triggers

(1) Triggers for referential constraint actions

If you specify CASCADE for a referential constraint action, HiRDB internally generates a trigger that updates the referencing table for the referenced table. Triggers generated internally by HiRDB become disabled in the following cases. In such a case, you need to re-create the trigger. You may need to create other triggers in addition to those that were generated by HiRDB. Use ALTER ROUTINE to re-create all triggers that have been disabled.

The triggers internally created by HiRDB are deleted when the referencing table is dropped (by DROP TABLE or DROP SCHEMA).

(2) Relationship between referential constraints and user-defined triggers

The following explains the order of the operation of triggers, integrity checking for referential constraints, and referential constraint operations (triggers generated internally by HiRDB when a referential constraint is defined) when a trigger and referential constraint are defined for a table, and an update SQL (INSERT statement, UPDATE statement, or DELETE statement) is to be executed. There are two operation order patterns, which depend on the following conditions:

Condition for pattern 1:
The update target is the referenced table and only RESTRICT is specified for the referential constraint action, or the update target is the referencing table.
Condition for pattern 2:
The update target is the referenced table and the referential constraint action is not RESTRICT.

If the update target is the referencing table and is also the referenced table, the condition for the referenced table takes precedence.

The order of the actions for each of the patterns is described below.

Pattern 1

[Figure]

#: All data integrity checking for the referential constraint takes place at this point. Following are the details of data integrity checking:
  1. When the update target is the referencing table
    Checking for whether the update (INSERT, UPDATE) data is contained in the referenced table
  2. When the update target is the referenced table
    Checking for whether the update (UPDATE, DELETE) data is contained in the referencing table
  3. When the update target is the referencing table and is also the referenced table
    Checking of both 1 and 2 above

Pattern 2

[Figure]

#: All data integrity checking for the referential constraint takes place at this point. Details of the integrity checking are the same as for pattern 1.