Example: The OLD() Function and Constraint Recursivity

The following two constraints are recursive if both constraints are active. If the user sets columnB to NULL, the first constraint is evaluated, which in turn causes evaluation of the second constraint. If the subquery in the second constraint returns NULL, then this causes evaluation of the first constraint again, and so on.

UPDATE    TableA

SET       ColumnA = 0

WHERE     ColumnB IS NULL

AND       OLD( ColumnB ) IS NOT NULL

UPDATE    TableA

SET       ColumnA = 10

,         ColumnB = 

(

      <subquery>

)

WHERE     ColumnA = 0

The fact that this example is recursive when the subquery returns NULL illustrates that the OLD() function continues to return the initial value changed by the user. The manipulation carried out by the second constraint resets the "new" value of column B, but OLD(ColumnB) is not immediately set to NULL.

Using a column value (ColumnA = 0) to cause constraint evaluation reveals a procedural structure which is perhaps better dealt with by using decisions, a special "flag", or status fields dedicated to rules evaluation.

NOTE 1: The same example could be condensed into a single constraint:

UPDATE    TableA

SET       ColumnA = 10

,         ColumnB = 

(

      <subquery>

)

WHERE  ColumnB IS NULL

AND    OLD( ColumnB  ) IS NOT NULL

This version is still conceptually recursive if the subquery returns NULL. Interestingly, however, in reality it does not lead to an error message because:

•The subquery is first evaluated to NULL.

•In the main statement, the subquery is substituted by NULL.

•The constraint is evaluated a first time (if ColumnA does not already have the value 10).

•When the constraint is about to be evaluated recursively, the constraint optimizer is able to see that the constraint will never set any column values to anything else other than what is already there. It does this by comparing current values to possible constraint output. As a result, it rejects further evaluation of the constraint, thereby incidentally avoiding recursion.

NOTE 2: The underlying data model of this example can probably be improved. It may be preferable to update columnB if the value of some "flag" column changes, rather than value change in columnB itself:

UPDATE     TableA

SET        ColumnB = 

(

       <subquery>

)

WHERE      FlagX = <value>

AND        OLD( FlagX ) = <value>