Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Delete trigger help please
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