Re: Update Cascade trigger

Peter, Look into "set constraints all deferred". Constraints won't be checked until the end of the transaction. This is available in 7.x versions of online, but I don't know about 5.x or SE. If it's not available in your version then you will have to resort to horrible solutions like inserting a new parent row, updating the children to point to the new parent and finally deleting the old parent. The underlying question that you may want to ask is "why is the database designed in such a way that I have to update the primary key of a parent table?". It's a good idea to use serial primary keys that never change that are "invisible" to the user but are used internally for references etc. A "visible" unique key can also exist in the table which can be modified without complications. Best wishes, ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl john@rl.is http://www.rl.is/~john/pow4gl.html 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? > > Thanks in advance > > -- > Peter > __________________________________ > Peter Marley > DBA, Acco UK Ltd > Tel: +44 (0)1296 397444 ext. 4098 > Fax: +44 (0)1296 311019 > email: peter.marley@acco-uk.co.uk