Re: Trigger for cascading deletes failing

From: Hello <Hello_at_yahoo.com>
Date: Tue, 07 Mar 2000 12:28:19 -0800
Message-ID: <38C56663.F42DA35E@yahoo.com>

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:28:19 CST