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

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

Re: Trigger for cascading deletes failing

From: Hello <Hello_at_yahoo.com>
Date: 2000/03/08
Message-ID: <38C6CBD7.83223917@yahoo.com>#1/1

DNP -- I agree with you wholeheartedly.
This app was pushed out so fast, there wasn't much time, from what I understand, to protest.
The main developer is actually a consultant (big mistake in my eyes -- nothing against them, but what happens to application support?).... There have been additional contractors/developers brought in to assist in the project, but it seems any time they questioned the design of the database/application and attempted to talk about rebuilding the mess, they mysteriously disappeared.....*SIGH*. I agree about the triggers especially -- they cause a lot of overhead. This database also has some huge packages -- at one time I had to send some trace files to Oracle support because the database was core dumping, and their first comment was "I sure hope you aren't the developer on this database -- these packages are outrageous".

So, the mess of the database is finally going to come to a head... wish me luck :-)

DNP wrote:

> 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 - 00:00:00 CST

Original text of this message

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