Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Trigger for cascading deletes failing

Re: Trigger for cascading deletes failing

From: DNP <High.Flight_at_btinternet.com>
Date: Wed, 08 Mar 2000 21:32:45 +0000
Message-ID: <38C6C6FD.5CB@btinternet.com>


This thread illustrates what seems to me to be a mess of a logical data model. P.S. why not get the Referential Integrity constraints on all child tables to CASCADE DELETE their own rows when the parent row is deleted. This type of Integrity constraint is much better than triggers. (you can also query the data dictionary to actually expose the ref. integrity constraints - can't do this easily with triggers).

One motto I have - if solving a problem starts to look like a bowl of sphagetti - reduce ( as in 'reductio ad absurdam' ) or simplify the starting assumptions (in this case the data model) and start again.

Try and get you application developers to develop an app without using any triggers or stored procedures. Once they can do this then the whole data model will be much more elegant (because it had to be).

Also - make sure they've all read Boyce-Codds 1970 Papers.


Hello wrote:
>
> Ah, I see what you are saying.
> So, Oracle protects it's data integrity by not allowing Orphan records....that
> makes perfect sense.
> I think this "workaround" was implemented because there was a case for one of
> the triggers where the parent and child were in the same table, thus you would
> get the "mutating table" error message.
> Thanks for the help.
>
> Alan Shein wrote:
>
> > You are trying to delete a "child" record and cascade the deletion upward to
> > the "parent". Can't do that. You need to delete the "parent" record and have
> > the deletion cascade downward to the "child" record. For example, suppose
> > you have an invoice/line item situation. The invoice table is the parent,
> > and the line items are children. What you are tring to do now is delete the
> > parent (invoice record) just because you deleted one child (line item). The
> > other children (line items) become orphans (they have no invoice record to
> > belong to.)
> >
> > You would need to delete the invoice record and cascade the deletes to the
> > line items.
> >
> > OR, you need a more sophisticated program that would take manage the reverse
> > process by keeping track of the invoice number of the line item, find all
> > other line items belonging to that invoice, delete those line items, and
> > then delete the invoice record.
> >
> > Hello wrote in message <38C54FA1.75B200C8_at_yahoo.com>...
> > >Running Oracle 8.0.5.2 on Solaris 2.7
> > >
> > >I am trying to implement a trigger given to me by developers that does
> > >cascading deletes.
> > >When testing through the java based application, I am receiving the
> > >following error message: (sorry, no Oracle number given).
> > >
> > >Attempt to orphan detail rows from <Table_name>. Master rows that have
> > >detail rows linked to them cannot be deleted or have their linking
> > >columns
> > >modified when cascading deletes and/or updates are disabled.
> > >I've looked, but I can't find what would cause this error, nor what can
> > >be done to correct it.
> > >
> > >Any ideas?
> > >
> > >TIA for any help
> > >
Received on Wed Mar 08 2000 - 15:32:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US