Oracle error 4091 error in pl/sql

i have to tables with this specification

s1(id1,id2,id3,asmt,rcpt,trsp)
s2(id1 ,id2 ,id3 ,extd)

supose that all feilds are varchar and id1,id2,id3 in s1 are unique with together
but in s2 are not.
these two tables link with id1,id2,id3 feilds.

i wanna write a row trriger that when i update the field rcpt in s1

if feild extd in s2 is '1' then the feild trsp in s1 fill with '400'


(in my application that connect to oracle first the fileds of id1,id2,id3,asmt from s1 and
  id1,id2,id3,extd from s2 will fill and in other stages other fields will fill)


so this is my triger

create or replace trigger s11
before update  of rcpt on s1 for each row
declare
n_count1    number;
begin
         if ((:old.asmt is not null) and (:old.rcpt is  null) and (:old.trsp is null) ) then
               select count(*) into n_count1 from s1,s2  where ((s2.id1=:old.id1) and (s2.id2=:old.id2) and (s2.id3=:old.id3) and (s2.extd='1'));
                if ((n_count1 >0) ) then  
                  :new.trsp := '400';
          end if;
         end if ;
end;


insert into s1
values('1','2','3','20',null,null);

insert into s2
values('1','2','3','1');


update s1
set rcpt ='500'
where ((id1='1') and (id2='2') and (id3='3'));

but when i excute an update statement in sqlplus in oracle i get ora 4091 error.

(mutating table)
how can i solve this problem please write the correct terriger code.
mhyassinAsked:
Who is Participating?
 
SujithData ArchitectCommented:
You dont have to join with s1 inside the trigger. The rowlevel values are already available in the :old variables. See the changed code.

create or replace trigger s11
before update  of rcpt on s1 for each row
declare
n_count1    number;
begin
         if ((:old.asmt is not null) and (:old.rcpt is  null) and (:old.trsp is null) ) then
               select count(*) into n_count1
               from s2  
               where ((s2.id1=:old.id1) and (s2.id2=:old.id2) and (s2.id3=:old.id3) and (s2.extd='1'));
                if ((n_count1 >0) ) then  
                  :new.trsp := '400';
          end if;
         end if ;
end;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.