Re: before delete, select trigger: table mutating error

From: srivenu <srivenu_at_hotmail.com>
Date: 25 Feb 2002 22:26:30 -0800
Message-ID: <1a68177.0202252226.768a2299_at_posting.google.com>

Hi,
The mutating error occurs for row level triggers. So you can do the following.
(forgive me for any syntax errors. Its a long time that i did any real PL/SQL programming and also i dont have any Oracle system on hand right now to test the code i wrote).

1. Create a package with a variable. Create or replace package PACK_PATIENT_DETAIL as type rx is record( st_no number, in_no number, pt_no number, no_of_rows number); type tx is table of rx; vx tx; end;
2. Write a Statement level trigger

create or replace TRIGGER trg_Patient_Visit_Detail_f2d   BEFORE DELETE ON patient_visit_detail_f  DECLARE
  cursor c1 is

     select study_number,investigator_number,patient_number,count(*)
     from patient_visit_detail_f;

 BEGIN
  PACK_PATIENT_DETAIL.vx := PACK_PATIENT_DETAIL.tx();   open c1;
  fetch c1 bulk collect into PACK_PATIENT_DETAIL.vx;  END; 3) Write a row level trigger
 create or replace TRIGGER trg_Patient_Visit_Detail_f2d   BEFORE DELETE ON patient_visit_detail_f   for each row
 DECLARE
  v_no_of_rows number;
 BEGIN

  for i in 1.. .PACK_PATIENT_DETAIL.vx.count loopp
    if  PACK_PATIENT_DETAIL.vx(i).st_no = :old.study_number
    and PACK_PATIENT_DETAIL.vx(i).in_no = :old.investigator_number
    and PACK_PATIENT_DETAIL.vx(i).pt_no = :old.patient_number     
then
       v_no_of_rows :=     and PACK_PATIENT_DETAIL.vx(i).no_of_rows;
       if v_no_of_rows <2 then
         raise_application_error(-20002,'Delete not allowed, update
 only.');
       else
         PACK_PATIENT_DETAIL.vx.v_no_of_rows:=v_no+of_rows-1;
       end if;
       exit;
     end if;

   end loop;
  end;     

Note: i did not take performance into consideration.

mliu007us_at_yahoo.com (Ming) wrote in message news:<4865d4fd.0202250843.4eedca0d_at_posting.google.com>...
> Hi,
> User is allowed to delete records, if they're not the last one in the
> group, otherwise will kick out an application error. I had the
> following codes, obviously I had table mutating error. How can I
> avoid it? I do want all next to last records in group deleted.
> Thanks.
>
> create or replace TRIGGER trg_Patient_Visit_Detail_f2d
> BEFORE DELETE ON patient_visit_detail_f
> for each row
> declare
> v_cnt number;
> BEGIN
> select count(*) into v_cnt
> from patient_visit_detail_f
> where study_number=:old.study_number and
> investigator_number=:old.investigator_number and
> patient_number=:old.patient_number;
> IF v_cnt<2 then
> raise_application_error(-20002,'Delete not allowed, update
> only.');
> end if;
> END trg_Patient_Visit_Detail_f2d;
Received on Tue Feb 26 2002 - 07:26:30 CET