Re: Trigger for cascading deletes failing

From: Hello <Hello_at_yahoo.com>
Date: 2000/03/07
Message-ID: <38C56307.60F016DD@yahoo.com>#1/1

Thomas, thanks.
I've already talked with developers on how it should work, :-))) and I believe Alan is right.
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; It's not the actual exception that is getting raised, it's an Oracle specific error.
The relationship is that basically there is one SALESOFFICE that can have many ADDRESS childs, and many Personel childs. That's why I think it looks like Alan is right....as the error is raised when trying to delete an ADDRESS from the application.

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