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
