[질문] 트리거 생성 후 commit 문이 들어 있는 패키지 호출시 트랜잭션이 맞지 않아 패키지가 이상하게 돕니다. 0 5 274

by sun [PL/SQL] 트리거 트랜잭션 trigger package PRAGMA AUTONOMOUS_TRANSACTION [2019.01.08 19:26:37]


안녕하세요. 이곳에서 정말 도움 많이 받고 있습니다. 늘 감사합니다. 

요지는 트리거에서 패키지를 실행하는데, 트리거에서는 패키지에서 참조하는 테이블에 값을 업데이트 해주는 로직이 있습니다. 

그리고 패키지는 그 테이블의 건수를 확인하여, 진행여부를 결정하고  패키지의 프로시저를 분기합니다. 

 

[T_CUST_MST_TEMP로 들어왔던 DATA 는 T_CUST_TEMP 로 INSERT 한 후 

T_CUST_MST_TEMP의 DATA 모두 삭제 후 PKG_CUST_INTEGRATE.SP_TEMP2CUST(V_MSG) 호출하는 트리거임.]

CREATE OR REPLACE TRIGGER TR_T_CUST_MST_AFT_STM_ALL
  AFTER INSERT ON T_CUST_MST_TEMP

  DECLARE  
    V_MSG VARCHAR2(4000);
    V_ERR NUMBER;
      
 BEGIN
 
     FOR I IN 1 .. PKG_CUST_CODE_TABLE.CUST_CTI_INDEX LOOP
          
          /* IC_CUST_TEMP로 이관 후 이관된 data는 T_CUST_MST_TEMP에서 삭제한다. */
          DELETE FROM T_CUST_MST_TEMP
          WHERE CUST_CODE = PKG_CUST_CODE_TABLE.CUST_CODE_NEW(I); 
        --DBMS_OUTPUT.PUT_LINE('CUST_CODE(NEW)=>'||TO_CHAR(PKG_CUST_CODE_TABLE.CUST_CODE_NEW(I)));     
     END LOOP;

     BEGIN      
        PKG_CUST_INTEGRATE.SP_TEMP2CUST(V_MSG); -- 패키지 호출
     END ;     
 END;

[PKG_CUST_INTEGRATE.SP_TEMP2CUST]

CREATE OR REPLACE PACKAGE BODY PKG_CUST_INTEGRATE
IS
   
     PROCEDURE SP_TEMP2CUST (PO_MSG OUT VARCHAR2)
     IS 
        
       생략....

       
       EXCEPTION_ERR EXCEPTION; 
       
        BEGIN 
           
           V_ERR_CODE := NULL;
           V_ERR_MSG  := NULL;

          /* 이 아래부분에서 COUNT(*) 갯수가 0이 나와서 진행이 안되되는 상황입니다. */   


           SELECT COUNT(*)
           INTO   V_IC_CNT
           FROM   T_CUST_TEMP
           WHERE  COLL_DATE = TO_CHAR(SYSDATE,'YYYYMMDD')
           AND    INTEGRATE_DATE IS NULL ;
         
           IF V_IC_CNT = 0 THEN 
              V_ERR_MSG := '고객통합 대상 없음.' ;
              PO_MSG := V_ERR_MSG ; 
              DBMS_OUTPUT.PUT_LINE(V_ERR_MSG);   
              
              RAISE EXCEPTION_ERR ;      
           END IF ;

          [프로시져 분기 예]

           IF X.CODE  = '10' THEN 

                   SP(1); 

           ELSEIF X.CODE = '20' THEN 

                  SP(2);
          ... 생략 

 

이미 트리거에서 INTEGRATE_DATE 컬럼은 NULL로 업데이트를 해놨는데,, 트리거의 트랙잭션이 아직 안끝난상태에서 패키지를 호출해서 그런지 

NULL처리된 건수를 찾질 못하고 있습니다. 

트리거에 패키지를 제거하고 트리거 따로, 패키지 따로 실행하면 잘됩니다만 , 트리거내에서 패키지를 호출하도록 해놨더니.. 안되고 있습니다. 

혹 이런 경우 다른 방법이 있는지요... 스케줄러를 이용하여 하려다가 실시간으로 하고 싶어 이런방법을 사용해봤더니.. 방법을 몰라서 그런지 

이곳저곳 자료를 찾아도 보이질 않습니다.. 

패키지의 프로시저 내에 COMMIT; 과 ROLLBACK; 이 있어 PRAGMA AUTONOMOUS_TRANSACTION; 옵션을 넣어서 테스트도 해봤는데.

아무래도 트랜잭션이 종료되지 않은 상태에서 해당 테이블을 SELECT 하니 조회가 안되는거 같아서 포기했습니다. 

고수님들. 방법이 있거나, 아님 이런 경우 스케줄을 쓰던지 다른 방법을 좀 알려주시면 감사하겠습니다. 

읽어주셔서 감사합니다. 

 

 

 


     

by 마농 [2019.01.08 19:45:46]

소스가 많이 생략되었네요.
T_CUST_TEMP 테이블에 대한 입력이나 갱신하는 부분이 안보이네요.
커밋 롤백 부분도 안보이고.
카운트 쿼리만 보고는 판단이 안되겠는데요.
그리고 트리거 내에서 커밋/롤백하면 안됩니다.

-- 추가 --
다시 생각해 보니 트랜잭션 문제인 듯 하네요.
- 독립 트랜잭션 으로 하면 데이터가 안보일 것 같습니다.
- 동일 트랜젝션 으로 하면 커밋/롤백을 지워야 합니다.


by sun [2019.01.08 22:35:02]

마농님. 빠른 답변에 정말 감사드립니다.

다 못올려 드려 죄송합니다. 도움을 요청하자면 다 올렸어야 하는데.. 

T_CUST_TEMP는 아래 트리거의 붉은색 글씨로 표시했습니다.

각 행단위별로 INSERT 또는 UPDATE를 하고 마지막에 문장단위 트리거에서 T_CUST_MST_TEMP 테이블의 자료를 모두 삭제 후 패키지를 호출하는 구조입니다. 

트리거 내에는 COMMIT; 이나 ROLLBACK;은 없습니다. 트리거에서 호출하는 패키지에 COMMIT이나 ROLLBACK이 있습니다. 

패키지 내에 COMMIT이나 ROLLBACK이 있다보니, 호출시 에러가 나더라구요.. 

그래서 이 패키지 내의 실행되는 프로시저에 PRAGMA AUTONOMOUS_TRANSACTION;을  사용해보니, 

         SELECT COUNT(*)
           INTO   V_IC_CNT
           FROM   T_CUST_TEMP
           WHERE  COLL_DATE = TO_CHAR(SYSDATE,'YYYYMMDD')
           AND    INTEGRATE_DATE IS NULL ;
         
           IF V_IC_CNT = 0 THEN 
              V_ERR_MSG := '고객통합 대상 없음.' ;
              PO_MSG := V_ERR_MSG ; 
              DBMS_OUTPUT.PUT_LINE(V_ERR_MSG);   
              
              RAISE EXCEPTION_ERR ;      
           END IF ;

위  쿼리의 COUNT( )가 0이 나오더라구요. 독립트랜잭션이 되서 그런거 같습니다. 

PRAGMA AUTONOMOUS_TRANSACTION;를 안쓰면 에러가 나고 쓰면 트랜잭션이 달라 자료가 안나오고.. 그렇습니다. 

트리거 실행되면서 바로 패키지를 실행해보려한건데요.. 패키지 안에 ROLLBACK .. 이 있어서 패키지 내에서 PRAGMA AUTONOMOUS_TRANSACTION; 까지 

써봤던 것입니다. 

좋은 방법이 있으면 조언 좀 부탁드리겠습니다. 

다시한번 읽어주셔서 감사합니다. 

 

[TRIGGER]

CREATE OR REPLACE TRIGGER ICUBE.TR_T_CUST_MST_AFT_ROW_ALL
 AFTER INSERT ON T_CUST_MST_TEMP
 FOR EACH ROW

  
 DECLARE 
 V_CNT NUMBER; 
 
 V_ERR_CODE     VARCHAR2 (100);
 V_ERR_MSG      VARCHAR2 (4000);
 EXCEPTION_ERR  EXCEPTION   ;
 
 BEGIN 
     PKG_CUST_CODE_TABLE.CUST_CTI_INDEX := PKG_CUST_CODE_TABLE.CUST_CTI_INDEX + 1;
     PKG_CUST_CODE_TABLE.CUST_CODE_CTI_OLD(PKG_CUST_CODE_TABLE.CUST_CTI_INDEX) := :OLD.CUST_CODE;
     PKG_CUST_CODE_TABLE.CUST_CODE_CTI_NEW(PKG_CUST_CODE_TABLE.CUST_CTI_INDEX) := :NEW.CUST_CODE;
     
         
       BEGIN 
          SELECT COUNT(*)
          INTO V_CNT 
          FROM T_CUST_TEMP
          WHERE CUST_CODE = :NEW.CUST_CODE;          
       END; 
       
       /* I: 생성(신규) */
       IF :NEW.IUD = 'I' THEN 
          /* 해당 고객코드가 없을 경우에만 INSERT 함*/
         IF V_CNT = 0 THEN    
         
                BEGIN                 
                     INSERT INTO T_CUST_TEMP(
                                 CUST_CODE
                                ,CUST_NAME
                                ,REG_NUMB
                                ,DEPT_NAME
                                ,PSIT_NAME
                                ,TEL_NUMB
                                ,MOBL_NUMB
                                ,FAX_NUMB
                                ,EXTS_NUMB
                                ,MAIL_ADDR
                                ,ZIP_NUMB
                                ,ADDRESS1
                                ,ADDRESS2
                                ,GENDER
                                ,PRIV_AGREE_YN
                                ,PRIV_AGREE_DATE
                                ,PRIV_EXPIRE_DATE                                
                                ,AD_INFO_YN
                                ,AD_EXPIRE_DATE
                                ,IUD                
                                ,COLL_GB
                                ,COLL_DATE
                                ,RMRK
                                ,INPT_DATE
                                ,INPT_EMPL
                                ,UPDT_DATE
                                ,UPDT_EMPL
                                )
                         VALUES  (
                                 :NEW.CUST_CODE 
                                ,:NEW.CUST_NAME 
                                ,:NEW.REG_NUMB 
                                ,:NEW.DEPT_NAME 
                                ,:NEW.PSIT_NAME  
                                ,:NEW.TEL_NUMB   
                                ,:NEW.MOBL_NUMB 
                                ,:NEW.FAX_NUMB   
                                ,:NEW.EXTS_NUMB 
                                , LOWER(:NEW.MAIL_ADDR)
                                ,:NEW.ZIP_NUMB
                                ,:NEW.ADDRESS1
                                ,:NEW.ADDRESS2 
                                ,:NEW.GENDER
                                ,:NEW.PRIV_AGREE_YN
                                ,:NEW.PRIV_AGREE_DATE
                                ,:NEW.PRIV_EXPIRE_DATE                                
                                ,:NEW.AD_INFO_YN
                                ,:NEW.AD_EXPIRE_DATE
                                ,:NEW.IUD               
                                , '10' /* 10.CTI고객*/
                                , TO_CHAR(SYSDATE,'YYYYMMDD')
                                , :NEW.RMRK
                                , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                                ,'CTI'
                                , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                                ,'CTI'               
                                ) ;
                   
                   EXCEPTION
                         WHEN OTHERS THEN
                           V_ERR_CODE:= SQLCODE ; 
                          
                           RAISE EXCEPTION_ERR ;  
                                    
                END ;               
            
         END IF ;
       /* U: 고객정보 수정 */
       ELSIF :NEW.IUD = 'U' THEN    
  
              IF V_CNT = 0 THEN  
                  
                  BEGIN 
                  
                        INSERT INTO T_CUST_TEMP(
                                     CUST_CODE
                                    ,CUST_NAME
                                    ,REG_NUMB
                                    ,DEPT_NAME
                                    ,PSIT_NAME
                                    ,TEL_NUMB
                                    ,MOBL_NUMB
                                    ,FAX_NUMB
                                    ,EXTS_NUMB
                                    ,MAIL_ADDR
                                    ,ZIP_NUMB
                                    ,ADDRESS1
                                    ,ADDRESS2
                                    ,GENDER
                                    ,PRIV_AGREE_YN
                                    ,PRIV_AGREE_DATE
                                    ,PRIV_EXPIRE_DATE 
                                    ,AD_INFO_YN
                                    ,AD_EXPIRE_DATE
                                    ,IUD                
                                    ,COLL_GB
                                    ,COLL_DATE
                                    ,RMRK
                                    ,INPT_DATE
                                    ,INPT_EMPL
                                    ,UPDT_DATE
                                    ,UPDT_EMPL
                                    )
                                VALUES  (
                                     :NEW.CUST_CODE 
                                    ,:NEW.CUST_NAME 
                                    ,:NEW.REG_NUMB 
                                    ,:NEW.DEPT_NAME 
                                    ,:NEW.PSIT_NAME  
                                    ,:NEW.TEL_NUMB   
                                    ,:NEW.MOBL_NUMB 
                                    ,:NEW.FAX_NUMB   
                                    ,:NEW.EXTS_NUMB 
                                    , LOWER(:NEW.MAIL_ADDR)
                                    ,:NEW.ZIP_NUMB
                                    ,:NEW.ADDRESS1
                                    ,:NEW.ADDRESS2 
                                    ,:NEW.GENDER
                                    ,:NEW.PRIV_AGREE_YN
                                    ,:NEW.PRIV_AGREE_DATE
                                    ,:NEW.PRIV_EXPIRE_DATE
                                    ,:NEW.AD_INFO_YN
                                    ,:NEW.AD_EXPIRE_DATE
                                    ,:NEW.IUD             
                                    , '10' /* 10.CTI고객*/
                                    , TO_CHAR(SYSDATE,'YYYYMMDD')
                                    ,:NEW.RMRK
                                    , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                                    ,'CTI'
                                    , TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                                    ,'CTI'               
                                    ) ;
                                    
                                    
                     EXCEPTION
                        WHEN OTHERS THEN
                           V_ERR_CODE:= SQLCODE ; 
                           RAISE EXCEPTION_ERR ;                                                   
                  END ;                  
                  
                  
              ELSE
                   
                   BEGIN 
                        /* 이미 등록되어 있는 자료를 테스트하는 중이라 이쪽을 타게 됩니다. 이쪽에서 INTEGRATE_DATE = NULL 업데이트 됩니다.  */
                         UPDATE T_CUST_TEMP
                         SET CUST_NAME         = :NEW.CUST_NAME 
                            ,REG_NUMB          = :NEW.REG_NUMB 
                            ,DEPT_NAME         = :NEW.DEPT_NAME 
                            ,PSIT_NAME         = :NEW.PSIT_NAME  
                            ,TEL_NUMB          = :NEW.TEL_NUMB  
                            ,MOBL_NUMB         = :NEW.MOBL_NUMB 
                            ,FAX_NUMB          = :NEW.FAX_NUMB 
                            ,EXTS_NUMB         = :NEW.EXTS_NUMB 
                            ,MAIL_ADDR         =  LOWER(:NEW.MAIL_ADDR)
                            ,ZIP_NUMB          = :NEW.ZIP_NUMB
                            ,ADDRESS1          = :NEW.ADDRESS1
                            ,ADDRESS2          = :NEW.ADDRESS2 
                            ,PRIV_AGREE_YN     = :NEW.PRIV_AGREE_YN     
                            ,PRIV_AGREE_DATE   = :NEW.PRIV_AGREE_DATE   
                            ,PRIV_EXPIRE_DATE  = :NEW.PRIV_EXPIRE_DATE   
                            ,AD_INFO_YN        = :NEW.AD_INFO_YN        
                            ,AD_EXPIRE_DATE    = :NEW.AD_EXPIRE_DATE              
                            ,IUD               = :NEW.IUD            
                            ,COLL_DATE         = TO_CHAR(SYSDATE,'YYYYMMDD')
                            ,INTEGRATE_DATE = NULL  => 이부분입니다. 
                            ,RMRK              = :NEW.RMRK
                            ,UPDT_DATE         = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
                            ,UPDT_EMPL         = 'CTI'                 
                         WHERE CUST_CODE = :NEW.CUST_CODE
                         AND   COLL_GB   = '10'; /* 출처구분 :CTI */     
                         
                         
                     EXCEPTION
                        WHEN OTHERS THEN
                           V_ERR_CODE:= SQLCODE ; 
                           RAISE EXCEPTION_ERR ;    
                     
                   END ;
              END IF ;

 

       
       EXCEPTION
          WHEN EXCEPTION_ERR THEN
                PKG_CUST_INTEGRATE.SP_INSERT_ERROR(sysdate, '10',:NEW.CUST_CODE,NULL,'TR_IC_CTITEMP_AFT_ROW_ALL',V_ERR_CODE,V_ERR_MSG);
          WHEN OTHERS THEN 
                PKG_CUST_INTEGRATE.SP_INSERT_ERROR(sysdate, '10',:NEW.CUST_CODE,NULL,'TR_IC_CTITEMP_AFT_ROW_ALL',SQLCODE ,SQLERRM);                 
                
     
 END;


by 마농 [2019.01.09 08:11:29]

이미 언급했듯이.
자치 트랜잭션으로는 안되므로
패키지 내 커밋/롤백을 제거해야 합니다.


by sun [2019.01.09 09:14:42]

답변 감사드립니다.

마농님 덕분에 일단 안되는거 확인이 되었습니다.

패키지 내에 DML들이 있는데, 커밋,롤백은 제거할 순 없고, job으로 생각을 해봐야겠습니다.

다시한번 감사드립니다.


by 마농 [2019.01.09 09:27:09]

커밋/롤백이 중간에 일부만 실행되는 형태인가요?
맨 마지막에 한번만 수행되는 형태라면? 제거하는게 맞을 듯 합니다.
제거하면 최초 입력 트랜잭션의 커밋/롤백을 따르겠지요.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입