| Subject | order of events in cascading foreign keys, triggers, position |
|---|---|
| Author | unordained |
| Post date | 2011-11-18T21:37:07Z |
http://pseudotheos.com/view_object.php?object_id=1619
Maybe this is documented in Helen's book (or elsewhere?) but I haven't seen a
flow diagram yet of the exact order in which cascade rules and regular triggers
are fired. So I reverse-engineered most of it by running test scripts, but I came
up with some blank spots, and I'm running out of steam to keep digging; can
anyone contribute further insight?
a) In what order are foreign key constraints (and their triggers) to a given
table processed, when updating/deleting that table? (By alpha constraint name, an
ID visible in rdb$ tables, or something entirely invisible to us? Is it even
deterministic?) Is it something designers could, if they needed to, tweak
themselves? Does the order care at all about the primary/non-primary status of
the referenced constraint? Are all FK's to PK1 processed together, before any
FK's to UQ1 and UQ2 (additional unique constraints), or are they all sorted
together somehow?
b) When are DEFAULT values assigned -- after all BEFORE triggers, or before the
first one?
c) Do multi-field constraints work any differently? (I doubt it.)
d) At what point are accurate calculated field values available? Are they
available on the NEW context variable, even as NEW is modified in a trigger?
e) Is there a better place for this discussion, or the resulting documentation?
Does it already exist (other than the source code)?
f) Is there a generalized solution for situations where on-delete of a child
record really needs data from its parent? (I guess a global temporary table could
store the old values of the parent, and the child could look there?)
g) I'm pretty sure trigger recursion works in the plainest way (that is, if you
update row x of A, and row y of A references x, it will be updated when its time
comes in the algorithm, and recurses immediately, with the same side-effects as
elsewhere in terms of selecting data from other rows while triggers are still
running; it's not strictly a parent/child issue), but does anyone have anything
to add on that topic?
-Philip
Maybe this is documented in Helen's book (or elsewhere?) but I haven't seen a
flow diagram yet of the exact order in which cascade rules and regular triggers
are fired. So I reverse-engineered most of it by running test scripts, but I came
up with some blank spots, and I'm running out of steam to keep digging; can
anyone contribute further insight?
a) In what order are foreign key constraints (and their triggers) to a given
table processed, when updating/deleting that table? (By alpha constraint name, an
ID visible in rdb$ tables, or something entirely invisible to us? Is it even
deterministic?) Is it something designers could, if they needed to, tweak
themselves? Does the order care at all about the primary/non-primary status of
the referenced constraint? Are all FK's to PK1 processed together, before any
FK's to UQ1 and UQ2 (additional unique constraints), or are they all sorted
together somehow?
b) When are DEFAULT values assigned -- after all BEFORE triggers, or before the
first one?
c) Do multi-field constraints work any differently? (I doubt it.)
d) At what point are accurate calculated field values available? Are they
available on the NEW context variable, even as NEW is modified in a trigger?
e) Is there a better place for this discussion, or the resulting documentation?
Does it already exist (other than the source code)?
f) Is there a generalized solution for situations where on-delete of a child
record really needs data from its parent? (I guess a global temporary table could
store the old values of the parent, and the child could look there?)
g) I'm pretty sure trigger recursion works in the plainest way (that is, if you
update row x of A, and row y of A references x, it will be updated when its time
comes in the algorithm, and recurses immediately, with the same side-effects as
elsewhere in terms of selecting data from other rows while triggers are still
running; it's not strictly a parent/child issue), but does anyone have anything
to add on that topic?
-Philip