OLD() Value During Constraint Processing

Each row event has its own OLD and NEW values for each of the columns in the record.

When column values are changed by constraints, the NEW value is obviously changed but the OLD value remains the same until the record is stored. This is true even if several corrective rules change NEW values within the record.

You therefore need to consider whether a constraint is handled before or after the record is stored. In general, the following constraint types are evaluated before the record is stored, as part of the current row event:

•Row Checks:

Restrictive constraints that only involve values within the current row, like for example restrictive single-record constraints.

•Update on Self constraints:

Corrective constraints that UPDATE values of the current row (other than subtype indicators), if the constraint is not Deferred.

Example 1

Consider the following three constraints:

Constraint 1, non-transitional:

UPDATE   <table>

SET      <column> = 2

WHERE    <column> = 1

Constraint 2, non-transitional:

UPDATE   <table>

SET      <column> = 3

WHERE    <column> = 2

Constraint 3, transitional:

UPDATE   <table>

SET      <column> = 10

WHERE    OLD( <column> ) = 2

The following table shows what happens if the user changes the column value from 0 to 1.

If the user changes the column value from 0 to 1, only the first two constraints are evaluated, and the record is stored AFTER the column value is set to 3. In this case, the third constraint will never be evaluated. This is because the first two constraints act as Update on Self constraints, and therefore do not cause the OLD value to change.

Example 2

Consider the following constraint:

UPDATE    <table>

SET       <column> = <column> + 1

Transition Table:  <table>

Fire On Insert:    Always

Fire On Delete:    Never

Fire On Update:    Never

Because this is a transitional constraint, the Rules Engine translates this constraint into:

UPDATE    <table>

SET       <column> = <column> + 1

WHERE     <primary_key_column> IS NOT NULL

AND       OLD( <primary_key_column> ) IS NULL

If the user inserts a record and enters column value 1, the OLD primary key column value is NULL. This causes evaluation of the constraint and changes the column value to 2.

After the column value has changed to 2, the OLD column value is still NULL and the manipulation is still considered as an INSERT. Therefore the constraint is evaluated again, despite the Fire On Update setting.

The constraint will be evaluated as many times as the Max Constraint Depth deployment configuration setting in the Authorizer (default 50) allows and will then result in an error message.

To prevent this problem:

•If you WANT to make the constraint recursive, add a stop condition like:

WHERE <column> < 10

•If you want to set the column to a default value when the user inserts a record, simply define a default value.

As soon as the record is stored, further corrective rules lead to new row events with their own series of OLD values. Such row events can be in different tables, but also in the same table as the initial row event. They may even involve records that were also manipulated as part of the initial row event. In this case, the OLD column values for the cascading row event may be different from the OLD values for the initial row event.

Changing the value of a subtype indicator leads to the creation of a new row event.