Resetting Default Transition Properties

See Also

After constraint checking, you may want to reset transition properties to improve performance by making sure the constraint is only evaluated 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 therefore:

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 following 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. (For INSERT, the outcome of the subquery is NULL and therefore the constraint is better not evaluated. For DELETE, presumably DAY records are deleted by cascading delete from SCHEDTOUR.)

Therefore you can improve performance by manually resetting the following properties:

Fire On Insert =    Never

Fire On Delete =    Never