데이터베이스 질문과 답변
안녕하세요. 이곳에서 정말 도움 많이 받고 있습니다. 늘 감사합니다.
요지는 트리거에서 패키지를 실행하는데, 트리거에서는 패키지에서 참조하는 테이블에 값을 업데이트 해주는 로직이 있습니다.
그리고 패키지는 그 테이블의 건수를 확인하여, 진행여부를 결정하고 패키지의 프로시저를 분기합니다.
[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 하니 조회가 안되는거 같아서 포기했습니다.
고수님들. 방법이 있거나, 아님 이런 경우 스케줄을 쓰던지 다른 방법을 좀 알려주시면 감사하겠습니다.
읽어주셔서 감사합니다.
소스가 많이 생략되었네요.
T_CUST_TEMP 테이블에 대한 입력이나 갱신하는 부분이 안보이네요.
커밋 롤백 부분도 안보이고.
카운트 쿼리만 보고는 판단이 안되겠는데요.
그리고 트리거 내에서 커밋/롤백하면 안됩니다.
-- 추가 --
다시 생각해 보니 트랜잭션 문제인 듯 하네요.
- 독립 트랜잭션 으로 하면 데이터가 안보일 것 같습니다.
- 동일 트랜젝션 으로 하면 커밋/롤백을 지워야 합니다.
마농님. 빠른 답변에 정말 감사드립니다.
다 못올려 드려 죄송합니다. 도움을 요청하자면 다 올렸어야 하는데..
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;
이미 언급했듯이.
자치 트랜잭션으로는 안되므로
패키지 내 커밋/롤백을 제거해야 합니다.
답변 감사드립니다.
마농님 덕분에 일단 안되는거 확인이 되었습니다.
패키지 내에 DML들이 있는데, 커밋,롤백은 제거할 순 없고, job으로 생각을 해봐야겠습니다.
다시한번 감사드립니다.
커밋/롤백이 중간에 일부만 실행되는 형태인가요?
맨 마지막에 한번만 수행되는 형태라면? 제거하는게 맞을 듯 합니다.
제거하면 최초 입력 트랜잭션의 커밋/롤백을 따르겠지요.
) 버튼을 클릭하여 작성 하시면 됩니다.