Transitional Constraints Without OLD()

Even if you do not use the OLD() function in constraint SQL, you can still set transition properties. There are two reasons why you might want to do this:

•You have written the constraint in such a way that it must only be evaluated when a particular table is manipulated, or else you get unwanted results. Set the transition table property to restrict constraint evaluation to this table. This only applies to multi-record constraints.

•You know the constraint need not be evaluated on an INSERT, UPDATE or DELETE on the transition table. By reducing the number of times a constraint is evaluated you improve application performance.

NOTE: You can also limit constraint handling to INSERTs by using the OLD() function. However, using transition property settings will result in better performance because the Rules Engine is able to discard the constraint (if it need not be evaluated) before it attempts to parse the SQL statement.

Example

The following constraint is used to calculate the return date of scheduled tours (table SCHEDTOUR):

UPDATE    schedtour st
SET       st.return_date =
(
    SELECT    st.start_date + t.num_days - 1
    FROM      tour t
    WHERE     t.destination = st.destination
    AND       t.tour_type = st.tour_type
)

Because the OLD() function has not been used, all transition properties are NULL. You know that in the Travel Agency, the number of days in table TOUR is always set before any start dates are entered into the system, because there is a restrictive constraint that blocks updates to the number of days when corresponding scheduled tours exist.

Restrict constraint evaluation to certain manipulations on SCHEDTOUR only:

Transition Table: SCHEDTOUR

Alias: ST

Fire On Insert: Always

Fire On Delete: Never

Fire On Update: UsedColumns