트리거연습2 오류

구매가 발생하면 , 해당 유저의 지금까지 구매한 총 금액을 저장하기 트리거를 작성중 발생한오류

 

처음 트리거를 작성할시

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
AFTER INSERT -- INSERT 발생 이후
ON BUY FOR EACH ROW --BUY 테이블에
-- BUY 테이블에 INSERT 발생 이후 각 ROW에 대하여
--액션을 수행 시켜줘라
BEGIN
	DECLARE total INT; --변수선언 
    DECLARE user_id INT DEFAULT NEW.user_id;
    -- INSERT 발생한 튜플에 user_id를 user_id에 넣어줘라
    --NEW
    --INSERT 된 TUPLE을 가리킴
    --UPDATE된 후의 TUPLE을 가리킴 
    select sum(price) into total from buy where user_id= user_id;
    -- INTO 키워드를 사용해서  total 변수에 저장
    update user_buy_stats set price_sum=total where user_id = user_id;
END;

위 처럼 작성을 하였었다 그리고 

등록도 잘 되었다

 

하지만 등록을 하는 순간

 

바로 해당 에러가 발생하였다

과거에 보았었던 구문이 잘못되었다는 문구였다

그래서 구문에 어떤 오류가 있나 찾아보았다

 

CREATE [OR REPLACE] TRIGGER <트리거 이름>

{BEFORE|AFTER}<이벤트>ON<테이블>

[FOR EACH ROW]

[WHEN (<조건>)]

[DECLARE <변수선언>]

BEGIN

         <실행코드>

[EXCEPTION <예외사항>]

END <트리거 이름>;

 

찾아보니 변수 선언 위치가 잘못된것을 발견할 수 있었습니다

그래서 다음과 같이 옮겨보았습니다

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
AFTER INSERT
ON BUY FOR EACH ROW
DECLARE 
total NUMBER; --변수선언 
user_id VARCHAR2 DEFAULT NEW.user_id;
BEGIN
    select sum(price) into total from buy where user_id= user_id;
    update user_buy_stats set price_sum=total where user_id = user_id;
END;

또 잘 등록이 되었고

다시 한번 등록을 해보니 또 

같은 에러가 발생하였다

select * from user_errors where type = 'TRIGGER' and name = 'SUM_BUY_PRICES_TRIGGER';

를 사용해서 확인해보니

user_id VARCHAR2 DEFAULT NEW.user_id; 이 부분이 잘못된것을 확인 할 수 있었다

 

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
AFTER INSERT
ON BUY FOR EACH ROW
DECLARE 
total NUMBER; --변수선언 
user_id VARCHAR2(200) DEFAULT :NEW.user_id;
BEGIN
    select sum(price) into total from buy where user_id= user_id;
    update user_buy_stats set price_sum=total where user_id = user_id;
END;

수정해서 실행해보니..

또 똑같이 실패하였다

하지만 에러가 변경되었다

[Oracle-오류] ORA-04091: table is mutating, trigger/function may not see it (테이블이 변경되어, 트리거/함수가 볼 수 없습니다)

 

트리거가 실행중인 테이블을 참조하려할때 발생한다

 

해결방법은  AFTER를 사용해서 업데이트가 완료되고 테이블에 접근하는 방법을 사용하라고 하였다

하지만 이미 AFTER를 사용하고있었다

 

그래서 이가 없으면 잇몸으로 해도자는 마인드로

SELECT를 없에고 업데이트만 남겨서 제작해보았다

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
AFTER INSERT ON BUY 
FOR EACH ROW
BEGIN
	IF INSERTING THEN
    UPDATE USER_BUY_STATS SET price_sum =price_sum+ :NEW.price WHERE user_id = :NEW.user_id;
    END IF;
END SUM_BUY_PRICES_TRIGGER;

이 처럼 하면 잘 동작하였다

 

하지만 원하는것은 조회를 하여 총 값을 저장하는 것을 원했기 때문에

방법을 생각해 보다가 테이블이 동작중이라 안된다는 것은 BEFORE 사용하면 동작을 하나?가 궁금해져서

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
BEFORE INSERT ON BUY 
FOR EACH ROW 
DECLARE  
TOTAL NUMBER;
BEGIN 
	SELECT SUM(price) INTO TOTAL FROM BUY WHERE user_id = :NEW.user_id;
    UPDATE USER_BUY_STATS SET price_sum = TOTAL WHERE user_id = :NEW.user_id;
     EXCEPTION
   	WHEN OTHERS THEN
   		BEGIN
	   		DBMS_OUTPUT.PUT_LINE('Exception:'||SQLERRM);
	   END; 
END SUM_BUY_PRICES_TRIGGER;

동작을 시켜보니 잘 동작이 되는 것을 확인할 수 있었다

그런데 자세히 보니 :NEW.user_id  NEW키워드를 사용해서 잘 동작된 것을 알 수 있었다

 

결국 

CREATE OR REPLACE TRIGGER SUM_BUY_PRICES_TRIGGER
BEFORE INSERT ON TEEMO.BUY 
FOR EACH ROW 
DECLARE  
TOTAL NUMBER;
BEGIN 
	IF INSERTING THEN
	SELECT SUM(price) INTO TOTAL FROM TEEMO.BUY WHERE user_id = :NEW.user_id GROUP BY price;
	--DBMS_OUTPUT.PUT_LINE('price:'||TOTAL);
    UPDATE TEEMO.USER_BUY_STATS SET price_sum = TOTAL+:NEW.price WHERE user_id = :NEW.user_id;
 END IF;
   EXCEPTION
   	WHEN OTHERS THEN
   		BEGIN
	   		DBMS_OUTPUT.PUT_LINE('Exception:'||SQLERRM);
	   END;  
END SUM_BUY_PRICES_TRIGGER;

이 처럼 TOTAL+:NEW.price를 해서 저장하니 잘 동작하였다