Changing Default Transition Properties

After constraint checking, you may want to change default transition properties to improve performance by making sure the constraint is evaluated only when you know this is necessary. For example only on UPDATE and not on INSERT or DELETE.

Example

For each day of a scheduled tour, that is, each day between the start_date and return_date in table SCHEDTOUR, there is a record in table DAY containing program details about the tour day. If the SCHEDTOUR dates are changed, you want the DAY dates to change along with them. Your constraint SQL statement is:

UPDATE    day d
SET       d.day_date =
(
    SELECT    d.day_date + (st.start_date - OLD( st.start_date ))
    FROM      schedtour st
    WHERE     st.schedtour_id = day.schedtour_id
)

The constraint checker produces the default transition properties:

Transition Table = SCHEDTOUR

Alias = ST

Fire On Insert = Always

Fire On Delete = Always

Fire On Update = UsedColumns

You know that in fact the constraint need not be evaluated on INSERT or DELETE. On INSERT, the outcome of the subquery is NULL so the constraint either has no effect or, if DAY_DATE is a mandatory column, will raise an error. On DELETE, presumably DAY records are deleted by cascading delete from SCHEDTOUR.

Change these 2 transition properties:

Fire On Insert = Never

Fire On Delete = Never