Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle error 4091 error in pl/sql

Posted on 2007-08-12
1
Medium Priority
?
1,100 Views
Last Modified: 2013-12-19
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.
0
Comment
Question by:mhyassin
1 Comment
 
LVL 27

Accepted Solution

by:
sujith80 earned 1000 total points
ID: 19678672
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month13 days, 2 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question