Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Delete trigger help please

Re: Delete trigger help please

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: 3 Nov 2000 09:01:02 GMT
Message-ID: <01c04574$a4e72af0$2c289a0a@apollo>

Sean <dolans_at_my-deja.com> skrev i artiklen <8tsu2k$258$1_at_nnrp1.deja.com>...
> I am trying to prevent deletion of certain data within a table that is
> identified with a SYS_PROT flag of 1. With a trigger, I would like to
> make sure that these fields are never deleted accidently. I can't seem
> to get it to work. Any help would be greatly appreciated:
>
> DELETEMEA
> ------------
> A_ID
> A_TEXT
> SYS_PROT
>
> CREATE OR REPLACE TRIGGER tdDeletemeA BEFORE DELETE ON deletemea
> FOR EACH ROW
> DECLARE
> numrows INTEGER;
> BEGIN
>
> select count(*) into numrows
> from deletemea
> where
> deletemea.a_id = :old.a_id;
>
> if (numrows = 0) then
> delete from deletemea where deletemea.a_id = :old.a_id;
> end if;
>
> END;
> /

Assuming you want to prevent deletion of rows with SYS_PROT=1 do something like (UNTESTED!):

CREATE OR REPLACE trigger triggname before delete on deletemea for each row
begin
  if (:old.sys_prot = 1) then
    raise_application_error(-20001, 'Cannot delete row with sys_prot = 1');   end if;
end;

Peter Laursen Received on Fri Nov 03 2000 - 03:01:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US