insert일어났을때 update하는 트리거를 작성했는데 ORA-04091: table is mutating, trigger/function may not see it 이오류가떠요
조회수 1820회
제가 한테이블에 insert가 일어나면 다른테이블에 평균값을 구해서 해당 컬럼을 update 하려고 하거든요!
트리거 문장은 아래와같은데 sqlplus에서 trigger문의 오류검사했을때는 오류가없거든요?
create or replace trigger Afterinsertmoviescore
after insert on moviescore for each row
declare
avg_score number;
mid number;
begin
select round(avg(ms_score),0) into avg_score from moviescore where ms_mid=:new.ms_mid;
update movie set m_score = avg_score where m_number=:new.ms_mid;
dbms_output.put_line('m_score컬럼 업데이트 완료 :' ||avg_score);
end;
/
근데 웹에서 insert를 시키면
심각: Servlet.service() for servlet [appServlet] in context with path [/controller] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: ORA-04091: table JAVAJO.MOVIESCORE is mutating, trigger/function may not see it
ORA-06512: at "JAVAJO.AFTERINSERTMOVIESCORE", line 5
ORA-04088: error during execution of trigger 'JAVAJO.AFTERINSERTMOVIESCORE'
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into moviescore values(nvl(seq_moviescore.nextval,1),?,?,?,?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-04091: table JAVAJO.MOVIESCORE is mutating, trigger/function may not see it
ORA-06512: at "JAVAJO.AFTERINSERTMOVIESCORE", line 5
ORA-04088: error during execution of trigger 'JAVAJO.AFTERINSERTMOVIESCORE'
] with root cause
java.sql.SQLSyntaxErrorException: ORA-04091: table JAVAJO.MOVIESCORE is mutating, trigger/function may not see it
ORA-06512: at "JAVAJO.AFTERINSERTMOVIESCORE", line 5
ORA-04088: error during execution of trigger 'JAVAJO.AFTERINSERTMOVIESCORE'
이런 오류가 나는데 ! 왜 나는건지 궁굼해요....
-
(•́ ✖ •̀)
알 수 없는 사용자
댓글 입력