Mutating table issue in after row trigger

Question

I've created a package that contains one procedure and one function. Function is called by the trigger on insert after each row on the table REZULTAT.

create or replace trigger rezultat_compound
for insert on rezultat
compound trigger
 v_igid              rezultat.igra_id%type;
 v_reid              rezultat.id%type;
 v_jck               rezultat.jocker_broj%type;
 cursor cur_type is  rezultat%rowtype;
before each row is
begin
 v_reid:=rezultat_seq.nextval;
 :new.id:=v_reid;
 v_igid:=:new.igra_id;
end before each row;

after each row is
begin
 v_jck:=loto_7_47_pck.getJockerBroj(v_igid);
end after each row;

end rezultat_compound;

Function is supposed to calculate one of the columns for the inserted row, and its input parameter is ID of the inserted row which I used to select that particular inserted row into record type before calculation with record type.

function                getJockerBroj
( i_igra_id             igra.id%type
)
return                  rezultat.jocker_broj%type
is
 v_jocker            rezultat.jocker_broj%type;
 rezultat_rec        brojevi_type; 
begin
select  br1
     , br2
     , br3
     , br4
     , br5
     , br6
     , br7
     ,dopunski_broj
into   rezultat_rec
from   rezultat
where  igra_id=i_igra_id;
v_jocker:=
      (  substr(to_char(rezultat_rec.br1),-1, 1) 
      || substr(to_char(rezultat_rec.br2),-1, 1) 
      || substr(to_char(rezultat_rec.br3),-1, 1)
      || substr(to_char(rezultat_rec.br4),-1, 1)
      || substr(to_char(rezultat_rec.br5),-1, 1)
      || substr(to_char(rezultat_rec.br6),-1, 1)
      || substr(to_char(rezultat_rec.br7),-1, 1)
      )
      ;    
 return v_jocker;
 end;

This apparently causes mutating table error, even though the function is called after insert on each row.

How can I get around this and make the calculation with each inserted row in that function?

Answer 1

The trigger (and any function called by the trigger) is not allowed to query the rezultat table since the trigger is a row-level defined on the rezultat table. Doing so, as you've found, causes a mutating table exception.

Generally, you'd want to code your logic so that you are only referencing data from the :new pseudorecord. In this case, for example, you could define your function to accept 7 parameters and pass those parameter values from your trigger. Something like

CREATE OR REPLACE FUNCTION getJockerBroj (
  p_br1 in rezultat.br1%type,
  p_br2 in rezultat.br2%type,
  p_br3 in rezultat.br3%type,
  p_br4 in rezultat.br4%type,
  p_br5 in rezultat.br5%type,
  p_br6 in rezultat.br6%type,
  p_br7 in rezultat.br7%type
)
  RETURN rezultat.jocker_broj%type
IS
BEGIN
  RETURN substr(to_char(p_br1),-1, 1) 
      || substr(to_char(p_br2),-1, 1) 
      || substr(to_char(p_br3),-1, 1)
      || substr(to_char(p_br4),-1, 1)
      || substr(to_char(p_br5),-1, 1)
      || substr(to_char(p_br6),-1, 1)
      || substr(to_char(p_br7),-1, 1);
END;

Your trigger would then do something like

v_jck:=loto_7_47_pck.getJockerBroj(:new.br1, 
                                   :new.br2,
                                   :new.br3,
                                   :new.br4,
                                   :new.br5,
                                   :new.br6,
                                   :new.br7);