Re: PL/SQL Problem

Regarding the triggers: it would be a whole lot cleaner to augment your dependent tables with foreign key referential constraints back to the parent table, with ON DELETE CASCADE attribute. Then you won't need these triggers because Oracle will to the chore of sweeping up the depend records when the master record is deleted.

John Gasch

Moore wrote:
>
> This will not solve all your problems -
>
> For your procedure use "IS" in place of "AS". Also, use NUMBER in place of
> NUM(9). Finally, terminate your SQl statement w/ ';'.
>
> Good luck.
>
> On Wed, 29 Mar 2000 jweisen_at_my-deja.com wrote:
>
> > I'm trying to write a simple stored procedure that I can pass the acct
> > number too and will delete the associated record, but I'm having some
> > problems (being fairly new to PL/SQL).
> >
> >
> > CREATE OR REPLACE PROCEDURE PURGE (V_ACCT NUM(9))
> > AS
> > BEGIN
> > DELETE FROM ADMARC.NAD
> > WHERE ADMARC.NAD.ACCT_KEY = V_ACCT
> > END;
> >
> > I'm also having problems with a triggers on this table which deletes all
> > associated dependancies:
> >
> > CREATE OR REPLACE TRIGGER Cascade_NAD_Delete
> > AFTER DELETE ON ADMARC.NAD
> > FOR EACH ROW
> > DECLARE
> > V_ACCT ADMARC.NAD.ACCT_NBR%TYPE;
> > BEGIN
> > DELETE
> > FROM ADMARC.INVC
> > WHERE ADMARC.INVC.STA_NBR=V_ACCT;
> >
> > DELETE
> > FROM ADMARC.WO
> > WHERE ADMARC.WO.ACCT_KEY=V_ACCT;
> >
> > DELETE
> > FROM ADMARC.CNR
> > WHERE ADMARC.CNR.ACCT_KEY=V_ACCT;
> > END Cascade_NAD_Delete;
> >
> > Any help would be appreciated.
> >
> > Thanks,
> > John
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
> >
Received on Fri Mar 31 2000 - 10:02:02 CST