데이터베이스 질문과 답변
by 오재민 [SQL Query] after insert trigger [2012.11.05 21:28:04]
productnumber 가 product테이블의 PK인데요 첨에 입력 할 때 시퀀스 넘버로 값 넣어서
insert 한 후에 productcategory 값에 따라서 productnumber를 업데이트 해주는 트리거 인데 오류가 나네요..ㅜㅜ
1 행: ORA-04091: table HR.PRODUCT is mutating, trigger/function may not see it
ORA-06512: at "HR.SETPRODUCTNUMBER", line 24
ORA-04088: error during execution of trigger 'HR.SETPRODUCTNUMBER'
ORA-06512: at line 1
소스코드
create or replace
trigger setproductnumber after update of productnumber or insert on product
for each row
declare
i_category varchar2(40) := :new.productcategory;
i_productnumber int := :new.productnumber;
begin
if updating then
UPDATE order_line
set order_line.productnumber = :NEW.productnumber
where order_line.productnumber = :OLD.productnumber;
elsif inserting then
if i_category = '남자 상의 긴 옷' then
update product
set productnumber = (111*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '남자 상의 짧은 옷' then
update product
set productnumber = (112*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '남자 하의 긴 옷' then
update product
set productnumber = (121*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '남자 하의 짧은 옷' then
update product
set productnumber = (122*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '여자 상의 긴 옷' then
update product
set productnumber = (211*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '여자 상의 짧은 옷' then
update product
set productnumber = (212*10000000 + i_productnumber)
where productnumber = i_productnumber;
elsif i_category = '여자 하의 긴 옷' then
update product
set productnumber = (221*10000000 + i_productnumber)
where productnumber = i_productnumber;
else
update product
set productnumber = (222*10000000 + i_productnumber)
where productnumber = i_productnumber;
end if;
end if;
end;
이런 경우의 product 테이블을 mutating 테이블이라고 합니다. mutating 테이블이 되면 현재 변경되는 (트리거가 걸린) 테이블의 내용이 어떤 데이터로 변경될지 확실히 알 수 없는 불안정한 상태가 되므로 이런 일이 발생하지 않게 미연에 방지하기 위해 에러가 발생하는 겁니다.
질문의 코드를 보니 업데이트시 i_category 값에 따라 현재 로우의 productnumber 컬럼 값을 변경하고자 하는 것 같은데 그런 효과를 얻으려면 타이밍을 after로 할 것이 아니라 before에서 :new.productnumber 값을 update 문의 set 절에 사용한 값으로 변경해 주면 오라클이 update를 수행할 때 원래의 :new.productnumber 컬럼의 값이 아니라 before 트리거에서 설정한 새로운 :new.productnumber 값으로 update 해 주게 됩니다.
일단 아래처럼 변경해 봤는데 제대로 될지는 모르겠네요..^^
CREATE OR REPLACE TRIGGER
setproductnumber
BEFORE UPDATE OF
productnumber
OR INSERT ON
product
FOR EACH ROW
BEGIN
IF
UPDATING
THEN
UPDATE
order_line
SET
order_line.productnumber = :NEW.productnumber
WHERE
order_line.productnumber = :OLD.productnumber;
ELSIF
INSERTING
THEN
:NEW.productnumber =
CASE
:NEW.productcategory
WHEN '남자 상의 긴 옷' THEN 111*10000000 + :NEW.productnumber;
WHEN '남자 상의 짧은 옷' THEN 112*10000000 + :NEW.productnumber;
WHEN '남자 하의 긴 옷' THEN 121*10000000 + :NEW.productnumber;
WHEN '남자 하의 짧은 옷' THEN 122*10000000 + :NEW.productnumber;
WHEN '여자 상의 긴 옷' THEN 211*10000000 + :NEW.productnumber;
WHEN '여자 상의 짧은 옷' THEN 212*10000000 + :NEW.productnumber;
WHEN '여자 하의 긴 옷' THEN 221*10000000 + :NEW.productnumber;
ELSE 222*10000000 + :NEW.productnumber;
END;
END IF;
END;
-- Case 문은 아마도 Select 문에서만 사용 가능 할 것입니다.
-- PL/SQL 에서는 IF 문을 써야 할것입니다.
CREATE OR REPLACE TRIGGER setproductnumber
BEFORE UPDATE OF productnumber OR INSERT ON product
FOR EACH ROW
DECLARE
i_category VARCHAR2(40) := :new.productcategory;
i_productnumber INT := :new.productnumber;
i_temp INT;
BEGIN
IF UPDATING THEN
UPDATE order_line
SET order_line.productnumber = :NEW.productnumber
WHERE order_line.productnumber = :OLD.productnumber
;
ELSIF INSERTING THEN
IF i_category = '남자 상의 긴 옷' THEN
i_temp := 111;
ELSIF i_category = '남자 상의 짧은 옷' THEN
i_temp := 112;
ELSIF i_category = '남자 하의 긴 옷' THEN
i_temp := 121;
ELSIF i_category = '남자 하의 짧은 옷' THEN
i_temp := 122;
ELSIF i_category = '여자 상의 긴 옷' THEN
i_temp := 211;
ELSIF i_category = '여자 상의 짧은 옷' THEN
i_temp := 212;
ELSIF i_category = '여자 하의 긴 옷' THEN
i_temp := 221;
ELSE
i_temp := 222;
END IF;
:NEW.productnumber = i_temp * 10000000 + i_productnumber;
END IF;
END;
/
SQL에서는 CASE expression만 가능하고요.
그리고 :NEW 또는 :OLD로 액세스하는 컬럼이름은 변수와 같은 개념이므로 굳이 새롭게 변수를 선언할 필요는 없을 것 같습니다.
음.. 제가 Decode 와 착각했네요.
PL/SQL 에서 Decode 가 사용이 안되는걸 Case 도 안되는걸로 착각했습니다.
테스트 해보니 잘 되네요.
SQL 에서도 두가지 Case 방식 다 작동합니다.
8i 에서는 한가지 방식만 동작하고요, 9i 부터는 두가지 다 잘 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.