선생님 질문 TRIGGER(트리거) 사용시 ORA-04091 Table is Mutating 오류 발생
<프로시저 expdate>
create or replace trigger expdate
after insert or update or delete on stock
for each row
DECLARE
d_product stock.p_name%type;
d_date stock.e_date%type;
BEGIN
select p_name,e_date into d_product,d_date
from stock
where (e_date-8 > sysdate);
if (d_date-8 > sysdate) then
dbms_output.put_line('Please check' || d_product);
end if;
end expdate;
/
<프로시저 실행시킬 트리거 save_products>
create or replace trigger save_products
after insert or update or delete on stock
for each row
begin
expdate;
end save_products;
/
<Mutating 방지 패키지 pack1>
create or replace package save_pack
as
type subs_name is table of stock.p_name%type index by binary_integer;
type subs_date is table of stock.e_date%type index by binary_integer;
stock_old subs_name;
stock_new subs_name;
stock_old subs_date;
stock_new subs_date;
stock_index binary_integer;
end save_pack;
/
<Before 트리거 초기화 before_trig>
create or replace trigger before_trig
before insert or update or delete on stock
begin
save_pack.stock_index :=0;
end;
/
참고 사이트 : http://www.gurubee.net/article/53504
http://sanghayo.tistory.com/48
http://www.gurubee.net/article/51905