Re: Update Cascade trigger
Peter Marley wrote: > > Hi, > > I don't think this can be done, but I wonder if anybody has a workable > solution. > > I have four tables with a parent / child relationship, with the unique key > to each table being made in part from the key to the parent. > > table A ---> table B ---> table C ----> table D > > with table A being the parent to table B > table B being the parent to table C > table C being the parent to table D > > If a value in table A needs to be changed all the associated rows in table > B, C and D also need to be changed, but obviously the parent/child > constraints will not allow the change to happen, because the rows in B > would be referencing the 'old' value in table A which would invalidate the > constraint etc. > > What I would like is some way to cascade the update before the constraint > rule takes hold, but I don't think this is possible. > > One way would be to drop all constraints, write SQL to do all the updates > and then reapply the constraints, which is a bit messy. > > Can anyone supply a better method? If you are using transactions, then something like: BEGIN WORK SET CONSTRAINTS ALL DEFERRED UPDATE table A ...... UPDATE table B ...... UPDATE table C ...... UPDATE table D ...... COMMIT WORK Should work. Constraint checking will go back to IMMEDIATE at the COMMIT WORK. You can specifiy a list of constraint names instead of the ALL keyword, but ALL is more portable, and saves looking up constraint names. ;-) Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+