Related to bug #6123, Wisconsin Courts are now using triggers with
the workaround to be safe with the patch put forward by Tom, even
though they are still running with the earlier patch proposal by me
(which tolerates an UPDATE or DELETE of a row being deleted). The
general structure of the BEFORE DELETE triggers with cascading logic
was like this:
DECLARE return_value parenttable;
BEGIN return_value := OLD;
DELETE FROM childtable1 WHERE <child of parent>; IF FOUND THEN return_value := NULL; END IF;
DELETE FROM childtablen WHERE <child of parent>; IF FOUND THEN return_value := NULL; END IF;
IF return_value IS NULL THEN DELETE FROM parent WHERE <primary key matches OLD>; END IF;
RETURN return_value;
END;
This did not work for cases where the AFTER DELETE trigger performed
an action which was not idempotent because, while return_value was
NULL enough to enter that last IF clause, it was not NULL enough to
prevent the DELETE attempt and fire subsequent triggers. The
assignment of NULL to a variable with a record type doesn't assign a
"simple" NULL, but a record with NULL in each element. It seems
like a POLA violation that:
return_value := NULL; RETURN return_value;
behaves differently than:
RETURN NULL;
We've fixed the afflicted trigger functions by adding a RETURN NULL;
inside that last IF block, but:
- Is this behavior required by standard?- If not, do we want to keep this behavior?- If we keep this behavior, should
thetriggering operation be suppressed when (NOT return_value IS NOT NULL) instead of when (return_value IS NOT
DISTINCTFROM NULL)?
-Kevin
