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 statements is manipulated.