i have to tables with this specification
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
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 ;
insert into s1
insert into s2
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.
how can i solve this problem please write the correct terriger code.