Transitional Constraints on Supertypes and Subtypes
When you use a subtype table in a transitional constraint, the conditions under which the constraint are evaluated require special attention.
The Fire On ... flags react to what happens to the supertype-subtype constellation as a whole, not to what happens to the subtype data alone.
Each time new subtype data is entered, information about this event (such as a timestamp) must be logged in a special log table.
You want to log these events when the entire constellation including the supertype data is new (INSERT of the record constellation), but also when the supertype already exists (UPDATE of the record constellation).
The constraint SQL statement has the following form:
INSERT INTO log_table
WHERE subtype_indicator = 'Y'
AND (old(subtype_indicator) = 'N'or old(subtype_indicator) is null)
The correct transition properties for this constraint are:
Fire On Insert: Always
Fire On Delete: Never
Fire On Update: Used Columns
In the example, the transition table is the subtype table. It could equally well be the supertype table (appearing both in the FROM clause and in the Transition Table field). The subtype indicator is available to both the supertype table and the subtype table.
If the constraint had additional clauses referring to subtype attributes, you would HAVE to use the subtype table, which would then also be the transition table.
Transitional constraint evaluation for subtypes also depends on: