Changing default transition properties

Previous Next

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.


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



See also

Default transition properties

Transition properties

Transitional constraints