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.
- For update processing
- The definition of the referencing table was changed.
- An index was defined for the referencing table.
- An index of the referencing table was dropped.
- A trigger whose timing is UPDATE was created for the referencing table.
- For the referencing table, a trigger whose timing is UPDATE was deleted.
- For the table that is referenced by the referencing table, change was made to the table definition of the primary key column.
- For deletion
- The table definition of the referencing table was changed.
- An index was defined for the referencing table.
- An index of the referencing table was dropped.
- A trigger whose timing is DELETE was created for the referencing table.
- For the referencing table, a trigger whose timing is DELETE was deleted.
The triggers internally created by HiRDB are deleted when the referencing table is dropped (by DROP TABLE or DROP SCHEMA).
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]](figure/zu120395.gif)
- * All data integrity checking for the referential constraint takes place at this point. Following are the details of data integrity checking:
- When the update target is the referencing table
Checking for whether or not the update (INSERT, UPDATE) data is contained in the referenced table
- When the update target is the referenced table
Checking for whether or not the update (UPDATE, DELETE) data is contained in the referencing table
- When the update target is the referencing table and is also the referenced table
Checking of both 1 and 2 above
Pattern 2
![[Figure]](figure/zu120305.gif)
- * 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.