Transitional Constraints Without OLD()

Previous Next

See Also

Even if you do not use the OLD() function in constraint SQL statements, 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 should only be evaluated when a particular table is manipulated, or you would 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 even parsing 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 long before any start dates are entered into the system.

Therefore, restrict constraint evaluation to certain manipulations on SCHEDTOUR:

Transition Table:  SCHEDTOUR

Alias:             ST

Fire On Insert:    Always

Fire On Delete:    Never

Fire On Update:    UsedColumns