|
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
|
