Re: Protect record from deletion via trigger

On Jul 1, 11:52 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Jul 1, 10:20 am, "markus.schre..._at_gmail.com"
>
>
>
>
>
> <markus.schre..._at_gmail.com> wrote:
> > Hi all,
>
> > I have a question regarding delete triggers. is there a way to protect
> > one specific record of a table from deletion? The way to do it with a
> > view and instead triggers is not possible. Also i can't raise an
> > APPLICATION_ERROR, since the whole thing should not be visible from
> > application side. Is there a way to do this?
>
> > CREATE OR REPLACE
> > TRIGGER
> > PROTECT_REC
> > BEFORE DELETE ON TARGETTABLE
> > FOR EACH ROW
> > BEGIN
> >   IF (:old.PKCOL=1) THEN
> >    Null;
> >   END IF;
> > END;
>
> > Thanks.. :)
>
> Not without throwing an error since you state you cannot use a view
> and an instead of trigger.
>
> What is the business reason for this 'requirement'?
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Markus, I agree with David you need to be able to use raise_application_error in the delete row trigger if you have to protect the row in the database.

I have never tried this but if you have the Enterpirse Edition you could try setting up a delete policy (VPD/FGAC) that appends a condition to the delete statement that tests the row key value is not this one specific row. In this case no error would be returned.

You should always include your edition and full Oracle version in post because the practical responses often depend on this information.

HTH -- Mark D Powell -- Received on Tue Jul 01 2008 - 12:39:43 CDT