Corrective Multi-Record Constraints

See Also

If a manipulation takes place and the WHERE clause of a corrective multi-record constraint is true, the Rules Engine performs a correction. This correction consists of an INSERT, DELETE, or UPDATE statement. A corrective table constraint performs some kind of data manipulation and can therefore activate other constraints, or even itself (recursive constraint).

EXAMPLE:

In the Travel Agency system, suppose the return_date of a scheduled tour (table SCHEDTOUR) can be calculated by adding the number of tour days from table TOUR to the start_date of the scheduled tour (in table SCHEDTOUR). The following constraint takes care of this:

UPDATE    schedtour s

SET    s.return_date =

(

   SELECT    s.start_date + t.num_days

   FROM    tour t

   WHERE    t.destination = s.destination

   AND    t.tour_type = s.tour_type

)

Remember, the Rules Engine evaluates this constraint whenever anyone of the columns it refers to is manipulated.

OLD()

You can use the OLD() function, or you can set transition properties for the constraint, or both, in which case you turn the constraint into a transitional constraint.

You may not use OLD() on more than one table. Transitional constraints are only evaluated if this table (the transition table) is manipulated, not if another table mentioned in the constraint SQL is manipulated.