The RELATE Keyword in Transitional Constraints

When using the RELATE keyword in transitional constraints, and primary key or foreign key values are changed run time, the Rules Engine substitutes both the OLD and NEW values of the changed record in this constraint. In specific cases, this default behavior may lead to performance problems, or may result in unexpected violations.

To make the behavior of RELATE in transitional constraints more explicit, you can specify whether you want to execute the constraint for the old values or the new values only, by explicitly adding OLD or NEW to the RELATE construct. These keywords can be used with the transition table:

·	If NEW is specified, the join will be over the new values of the key.

·	If OLD is specified, the join will be over the old values of the key.

·	If neither NEW nor OLD is specified, the join will be over the old AND new values. This is similar to the default behavior of RELATE.

For example:

SELECT ''

FROM CHILD c, PARENT P

RELATE p with NEW c

WHERE P.col = 'Y'

This results in only the new foreign key values of CHILD being joined with PARENT. While:

SELECT ''

FROM CHILD c, PARENT P

RELATE p with OLD c

WHERE P.col = 'Y'

This results in only the old foreign key values of CHILD being joined with PARENT.

NOTES:

Note the following:

·	You can only use OLD and NEW on ONE table, the transition table.

·	You cannot use OLD and NEW in the SQL Definer.

·	The OLD keyword has different behavior to the OLD() function.