Current Row Counting

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: In 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.