PL/SQL problem error :"table ..xxx... is mutating, trigger/function may not see it

From: kuba <kuba_at_logolift.pl>
Date: Thu, 20 Oct 2005 15:18:49 +0200
Message-ID: <dj85fv$4st$1@atlantis.news.tpi.pl>

I have a problem with my trriger and coudl not find solution. Porblem is like this
I have a table:
"create table PRACOWNICY( ID_PRAC NUMBER(4) not null,

			  NAZWISKO    VARCHAR2(15),
			  ETAT        VARCHAR2(10),
			  ID_SZEFA    NUMBER(4),
			  ZATRUDNIONY DATE,
			  PLACA_POD   NUMBER(6,2),
			  PLACA_DOD   NUMBER(6,2),
			  ID_ZESP     NUMBER(2)

)
alter table PRACOWNICY

   add constraint PK_PRAC primary key (ID_PRAC); alter table PRACOWNICY

   add constraint FK_ETAT foreign key (ETAT)    references ETATY (NAZWA);
alter table PRACOWNICY

   add constraint FK_ID_SZEFA foreign key (ID_SZEFA)    references PRACOWNICY (ID_PRAC);
alter table PRACOWNICY

   add constraint FK_ID_ZESP foreign key (ID_ZESP)    references ZESPOLY (ID_ZESP);
alter table PRACOWNICY

   add constraint MIN_PRACA
   check (PLACA_POD>800);"

and second table :
"

create table ZESPOLY
(

   ID_ZESP NUMBER(2) not null,

   NAZWA     VARCHAR2(20),
   ADRES     VARCHAR2(20),

   LICZ_PRAC NUMBER(5),
   SRED_PLAC NUMBER(6,2)
)

alter table ZESPOLY

   add constraint PK_ZESP primary key (ID_ZESP)    ;"

i've created a package:

create or replace package a is
zesp_id_temp zespoly.licz_prac%type;
placa_sr_temp zespoly.sred_plac%type;
cursor curs_zesp is

   select id_zesp from zespoly;
end a;

end my trigger:
create or replace trigger tr_updater_audi after insert or delete or update on pracownicy

   for each row
--declare

begin

      FOR i IN a.curs_zesp
      LOOP
          begin
               SELECT COUNT(*) INTO a.zesp_id_temp   <- and here is error
               FROM pracownicy
               WHERE id_zesp = i.id_zesp;
          end;
          if (nvl(a.zesp_id_temp,0) = 0) then
             begin
                  UPDATE zespoly SET licz_prac = 0,
                                     sred_plac = 0
                  WHERE id_zesp = i.id_zesp;
             end;
          else
               begin
                    SELECT sum(placa_pod) INTO a.placa_sr_temp
                    FROM pracownicy
                    WHERE id_zesp = i.id_zesp;
                    a.placa_sr_temp:= a.placa_sr_temp / a.zesp_id_temp;
               end;
                  UPDATE zespoly SET licz_prac = a.zesp_id_temp,
                                     sred_plac = a.placa_sr_temp
                  WHERE id_zesp = i.id_zesp;

          end if;
      END LOOP;

end tr_updater_audi; Received on Thu Oct 20 2005 - 08:18:49 CDT