Hi to all!
There is a task: a person is added/removed to/from the list of members of the church ("Spiritual" table, column "church_inf"), or a person is moving to another church. With these changes, the number of members "quantity" in the respective churches must be recalculated ("Churches" table).
In order to avoid mutating tables, a package with public variables (gnch_inf, goch_inf, gnquant, goquant) and two triggers was created (string trigger "BIUDR_Spiritual" for using the :new and :old variables).
But when trying to execute a request
update "Spiritual" set "church_inf"=12
where "child"=9;
I still get this messages:
ORA-04091: table VRBC.Spiritual is mutating, trigger/function may not see it
ORA-06512: at "VRBC.PK_QUANT", line 7
ORA-06512: at "VRBC.BIUDR_Spiritual", line 2
ORA-04088: error during execution of trigger 'VRBC.BIUDR_Spiritual'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace package pk_quant as
gnch_inf "Spiritual"."church_inf"%type;
goch_inf "Spiritual"."church_inf"%type;
gnquant "Churches"."quantity"%type;
goquant "Churches"."quantity"%type;
procedure pr_vars
(nch\_inf "Spiritual"."church\_inf"%type,
och\_inf "Spiritual"."church\_inf"%type);
end pk_quant;
/
create or replace package body pk_quant as
procedure pr_vars
(nch\_inf "Spiritual"."church\_inf"%type,
och\_inf "Spiritual"."church\_inf"%type) is
begin
gnch\_inf := nch\_inf;
select "quantity" into gnquant
from "Churches" left join "Spiritual" on "church\_id"="church\_inf"
where "church\_id"=nch\_inf;
goch\_inf := och\_inf;
select "quantity" into goquant
from "Churches" left join "Spiritual" on "church\_id"="church\_inf"
where "church\_id"=och\_inf;
end pr_vars;
end pk_quant;
/
create or replace trigger "BIUDR_Spiritual"
before insert or update or delete on "Spiritual"
for each row
begin
pk\_quant.pr\_vars(:new."church\_inf", :old."church\_inf");
end "BIUDR_Spiritual";
create or replace trigger "AIUDT_Spiritual"
after insert or update or delete on "Spiritual"
begin
if inserting then update "Churches" set "quantity"=pk\_quant.gnquant+1
where "church\_id"=pk\_quant.gnch\_inf;
elsif updating then update "Churches" set "quantity"=pk\_quant.goquant-1
where "church\_id"=pk\_quant.goch\_inf;
update "Churches" set "quantity"=pk\_quant.gnquant+1
where "church\_id"=pk\_quant.gnch\_inf;
elsif deleting then update "Churches" set "quantity"=pk\_quant.goquant-1
where "church\_id"=pk\_quant.goch\_inf;
end if;
end "AIUDT_Spiritual";
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please help to learn how to work with mutating tables.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~