Unwanted New Value Substitution

Within transitional constraints, all SQL occurrences of transition table columns are substituted by the current OLD and NEW column values of the current row event.

This is important whenever there are multiple occurrences of the transition table in a constraint SQL statement.

Example:

Suppose you want to delete a parent record if the last child record is deleted. The constraint you have written reads:

DELETE FROM    parent p

WHERE NOT EXISTS

(

       SELECT    'child record'

       FROM      child c

       WHERE     c.foreignkey = p.primarykey

)

AND EXISTS

(

       SELECT    'old child record'

       FROM      child c

       WHERE     OLD( c.foreignkey ) = p.primarykey

)

and has transition properties:

Transition Table:  CHILD

Alias:             C

Fire on Insert:    Never

Fire on Delete:    Always

Fire on Update:    Never

This is a corrective, transitional constraint on a child table. If the last record of the child table is deleted, this constraint should make sure the parent record is automatically deleted as well. Because of the OLD() function in the last clause, the child table is automatically the Transition Table. The constraint is only evaluated when a child record is deleted, because of the transition properties.

Whenever a child record is deleted, the c.foreignkey column name becomes NULL, because that is the NEW value in a Delete. This makes the outcome of this subquery uncertain and dependent on your database conventions regarding treatment of NULL values. This is not what you want.

The only place where you want to bind a current value to the statement is in the second mention of the transition table (the OLD(c.foreignkey) value).

The solution is to use different table aliases so you can specify this:

DELETE FROM    parent p

WHERE NOT EXISTS

(

        SELECT    'child record'

        FROM      child c1

        WHERE     c1.foreignkey = p.primarykey

)

AND EXISTS

(

        SELECT   'old child record'

        FROM     child c2

        WHERE    OLD( c2.foreignkey ) = p.primarykey

)

and set transition properties:

Transition Table:  CHILD

Alias:             C2

Fire on Insert:    Never

Fire on Delete:    Always

Fire on Update:    Never