Time Constraints

USoft Production has an application timer that allows scheduled constraint execution. The timer consists of an application table T_APP_TIME, and an executable USCLOCK.EXE that updates T_APP_TIME with the current database time at regular intervals.

Example:

You can use a time constraint for future changes of customer addresses:

Suppose, customers can announce a new address and the date from which the new address takes effect. The application must automatically replace the old address with the new address at the right time.

This data is stored in a separate NEW_ADDRESS table:

MR_help0064

When the date arrives, the automatic update of the only record in the T_APP_TIME table causes the evaluation of a corrective SET_NEW_ADDRESS constraint that updates the PERSON table.

This constraint has SQL statement:

UPDATE     person p
SET
(
           p.address
,          p.city
,          p.area_code
) =
(

    SELECT     na.address
    ,          na.city
    ,          na.area_code

FROM new_address na

    ,          t_app_time tt
    WHERE      na.person_id = p.person_id
    AND        tt.app_year =  TO_NUMBER( TO_CHAR( na.from_date,'YYYY' ))
    AND        tt.app_month = TO_NUMBER( TO_CHAR( na.from_date,'MM' ))
    AND        tt.app_day =   TO_NUMBER( TO_CHAR( na.from_date,'DD' ))
)

Set transition properties:

Transition Table = T_APP_TIME

Fire On Insert = Never

Fire On Delete = Never

Fire On Update = UsedColumns

NOTE: Make sure correct comparison (data type conversion) takes place between the NUMBER data in the T_APP_TIME table and the DATE data in the FROM_DATE column. Consult your RDBMS SQL documentation.