Re: Trigger for cascading deletes failing

Basically the relationship is The Salesoffice owns one address and multiple phonecontacts.......relationed with the id and personel fields.

Hello wrote:

> An example of the trigger is:
>
> CREATE OR REPLACE TRIGGER TRG_CAS_DEL_SALESOFFICE BEFORE DELETE ON
> SALESOFFICE
> FOR EACH ROW
> DECLARE
> salesoffice_id SALESOFFICE.ID%TYPE;
> BEGIN
> IF DELETING THEN
> salesoffice_id:= :old.id ;
> dbms_output.put_line('salesoffice.id :'||salesoffice_id);
>
> DELETE FROM ADDRESS
> WHERE PERSONEL=salesoffice_id;
> DELETE FROM PHONECONTACT
> WHERE PERSONEL=salesoffice_id;
> END IF;
> EXCEPTION
> WHEN others THEN
> raise_application_error(-20000, 'Cannot Delete the Child
> components');
> END;
>
> "Thomas J. Kyte" wrote:
>
> > In article <38C54FA1.75B200C8_at_yahoo.com>,
> > Hello <Hello_at_yahoo.com> wrote:
> > > 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
> > >
> > >
> >
> > sounds like a "custom" error message they are throwing via
> > raise_application_error() (check their source -- its probably in
> > there). Sounds like, based on the error message, they have a stored
> > procedure or variable you must call/set in a package BEFORE having the
> > delete cascade take effect (eg: you must tell them via a stored
> > procedure call that you want them to cascade a delete -- it does not
> > happen by default).
> >
> > You really should ask the developers how it works ;)
> >
> > --
> > Thomas Kyte tkyte_at_us.oracle.com
> > Oracle Service Industries
> > http://osi.oracle.com/~tkyte/index.html
> > --
> > Opinions are mine and do not necessarily reflect those of Oracle Corp
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Tue Mar 07 2000 - 14:36:08 CST