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

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL problem error :"table ..xxx... is mutating, trigger/function may not see it

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

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Thu, 20 Oct 2005 16:00:34 +0200
Message-ID: <4357a328$0$12663$9b4e6d93@newsread4.arcor-online.net>


kuba schrieb:
> 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;
>
>
>
>
>
>
>
>
>
>
>
>

In general such problems ( mutating tables) can be solved on this way: You create an additional ( statement level ) trigger. You perform the select on the table being changed in this trigger. You store results of your calculations in the package variable. You read the package variable in the ( row level ) trigger, so you don't need to select the table being changed.

There can be also some other approaches to solve this problem.

But , in my opinion , what you are trying to implement will be a very expensive ( in term of resources usage ) operation. You could instead move the columns LICZ_PRAC and SRED_PLAC away from the table ZESPOLY and create an materialized view, which should compute these aggregates. You can make this view fast refresheable and refresh it ON COMMIT if a real need is here, if not - refresh it in certain intervals ( e.g hourly, or somewhat like this ).

Best regards

Maxim Received on Thu Oct 20 2005 - 09:00:34 CDT

Original text of this message

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