Current Row Counting

See Also

Transitional constraints are bound to the current record when they are handled:

In the constraint SQL statements of transitional constraints, for all occurrences of the transition table, only the current row event is considered.

Do not make a constraint transitional if a group function such as COUNT() is used in the SQL statement of the constraint. In that case, only the current row event would be counted.

Example

In the Travel Agency application, suppose you wanted to add an amount column to the TOUR table, in which is stored the current total number of scheduled tours for that tour. The correct amount must be calculated by a corrective constraint whenever a scheduled tour is added or removed. The constraint reads:

UPDATE    tour t

SET    t.amount =

       (SELECT    COUNT(st.schedtour_id)

        FROM    schedtour st

        WHERE    st.destination=t.destination

AND    st.tour_type=t.tour_type)

Suppose this constraint is made transitional by setting transition properties:

Transition Table:  SCHEDTOUR

Fire on Insert:    Always

Fire on Delete:    Always

Fire on Update:    Never

In this case, the result would be the same as if the constraint read:

UPDATE    tour t

SET    t.amount =

       (SELECT    COUNT(st.schedtour_id)

        FROM    schedtour st

        WHERE    st.destination=t.destination

AND    st.tour_type=t.tour_type)

AND    (OLD(st.schedtour_id) IS NULL

OR st.schedtour_id IS NULL)

       )

Now, only the scheduled tour records within the current row event are counted, which will always lead to a value for amount of 0 or 1.

To prevent this problem, simply do not make the constraint transitional.

If, for whatever reason, you still want to write a transitional constraint, you may change the constraint into:

UPDATE    tour t

SET    t.amount =

(

   SELECT  COUNT(st.schedtour_id)

   FROM    schedtour st

   WHERE   st.destination = t.destination

   AND     st.tour_type = t.tour_type

)

WHERE EXISTS

   (

       SELECT 'added or removed scheduled tour'

       FROM    schedtour st2

       WHERE    /* added scheduled tour */

(

   st2.destination = t.destination

           AND

   st2.tour_type = t.tour_type)

OR    /* removed scheduled tour */

(

   OLD(st2.destination) = t.destination

   AND

   OLD(st2.tour_type) = t.tour_type

)

   )

Set the transition properties to:

Transition Table: SCHEDTOUR

Alias:            st2

Fire On Insert:   Always

Fire On Delete:   Always

Fire On Update:   Never

Note the Alias value here. You only want the constraint to be evaluated if the SCHEDTOUR table with alias st2 is manipulated.

TIP:

If you change the Fire On Update attribute to Used Columns, this constraint is also evaluated when the foreign key of a scheduled tour is changed from one tour to another.