Recent Comments
Toobit Exchange: https://toobitexchange...
야튜브: What's up!
What a nice..
한국야동: Hi,
What a nice post!
..
야유브: Hi,
What an good blog!..
Stanmorenhq: from lat. manus - "hand..
Pouringkhu: Century to a kind of de..TRIGGER에 대해서 알아 보도록 하겠습니다.
1. 정의
| * STANDARD TABLE에서 DML작업이 일어 날 경우, BACKUP TABLE에 묵시적으로 실행되는 프로시져로써 두 테이블 간에 데이터를 동기화 시키기 위한 목적이 있습니다. * TRIGGER는 TABLE에 대해서만 정의가 가능하며, VIEW는 사용이 불가합니다. * 별도의 호출명령어는 필요 없으며 TRIGGER를 생성하기만 하면 일련의 행위가 일어났을 때, 실행 되는 방식입니다. |
1-1. 트리거 종류
| * 행트리거 - 컬럼의 각각의 행의 데이터 행 변화가 생길때 마다 실행되며, 그 데이터 행의 실제값을 제어 할 수 있습니다. * 문장트리거 - 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어 할 수 없습니다. |
2. TRIGGER 생성 문구
|
1 2 3 4 5 6 |
CREATE [OR REPLACE] TRIGGER 트리거명 -- Trigger 생성/ 재생성 BEFORE | AFTER -- 트리거 시작시점 지정(DML작업 전/DML작업 후) Trigger_Event ON Table_Name -- [INSERT / UPDATE / DELETE] 데이터 처리 형태 지정 [FOR EACH ROW] -- 트리거의 종류(FOR EACH ROW 입력: 행트리거 / FOR EACH ROW 생략: 문장트리거) [WHEN(Condition)] -- 데이터의 처리유형 이외의 조건을 삽입할 경우 PL/SQL Block -- Trigger가 발생되는 Body |
3. 트리거 삭제, 활성화, 비활성화
| * 삭제: DROP TRIGGER [트리거명]; * 활성화: ALTER TRIGGER [트리거명] ENABLE; * 비활성화: ALTER TRIGGER [트리거명] DISABLE; * 조회: SELECT * FROM DBA_OBJECTS WHERE OJBECT_TYPE = ‘TRIGGER’; SELECT * FROM USER_TRIGGERS; |
4-1. 테스트 시나리오_1
: STANDARD TABLE과 BACKUP TABLE간의 데이터들을 실시간으로 동기화 시키고 싶을 때.
1). TEST TABLE & DATA 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- ST_TABLE 생성 SQL> DROP TABLE ST_TABLE; DROP TABLE ST_TABLE ORA-00942: table or view does not exist SQL> CREATE TABLE ST_TABLE AS 2 SELECT LEVEL l_no 3 ,LENGTH(LEVEL) CNT 4 ,TO_DATE('0001-01-01', 'YYYY-MM-DD') + LEVEL - 1 CHILD_DATE 5 FROM DUAL 6 CONNECT BY LEVEL <= 100; Table created -- BACKUP TABLE 생성 SQL> DROP TABLE BU_TABLE; DROP TABLE BU_TABLE ORA-00942: table or view does not exist SQL> CREATE TABLE BU_TABLE AS 2 SELECT * 3 FROM ST_TABLE 4 ; Table created |
2). TRIGGER 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE OR REPLACE TRIGGER TO_BU_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON ST_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO BU_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); END IF; -- :NEW, :OLD를 통해 STANDARD TABLE에서 DML 작업 전 데이터와 후 데이터를 가져 올 수가 있습니다. -- 이는 INSERTED / DELETED TABLE에서 값을 가져오게 되는데 해당 테이블은 물리적으로 DB에 저장되어 있는 테이블이 아닌 임시적으로 사용되는 테이블입니다. -- INSERTED TABLE은 데이터가 INSERT 될때 만들어지고, DELETED TABLE은 데이터가 DELETE 될때 만들어 집니다. -- UPDATE는 내부적으로 처리될때 DELETE + INSERT이기 때문에 UPDATED TABLE은 존재 하지 않습니다. EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_BU_TABLE_TRIGGER; |
3). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
-- 삽입 할 데이터 존재 여부 확인. SQL> SELECT * FROM BU_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- DBMS_OUTPUT.PUT_LINE 확인 SQL> SET SERVEROUTPUT ON; -- 데이터 삽입 SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (101,1,SYSDATE); 1 row inserted -- 데이터 확인(같은 세션일 경우에만 가능) SQL> SELECT * FROM ST_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- 101 1 2015-01-22 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- 101 1 2015-01-22 -- ROLLBACK 했을 경우 SQL> ROLLBACK; Rollback complete SQL> SELECT * FROM ST_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- SQL> SELECT * FROM BU_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- 데이터 삽입 SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (101,1,SYSDATE); 1 row inserted -- COMMIT일 경우 SQL> COMMIT; Commit complete -- 데이터 확인 SQL> SELECT * FROM ST_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- 101 1 2015-01-22 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 101; L_NO CNT CHILD_DATE ---------- ---------- ----------- 101 1 2015-01-22 |
4). 테스트 결과
: ST_TABLE에서 Insert 후 Commit을 했을 경우 완전히 완료가 되지만
Rollback을 했을 경우에는 똑같이 BU_TABLE도 Rollback이 되는 것을 확인 할 수 있습니다.
4-2. 테스트 시나리오_2
: STANDARD TABLE을 참조하고 있는 프로그램이 300개가 있습니다.
STANDARD TABLE을 참조하고 있는 프로그램을 BACKUP TABLE로 나누어서 관리를 하고 합니다.
즉, STANDARD TABLE을 참조하는 프로그램은 200개, BACKUP TABLE을 참조하는 프로그램은 100개..
이런 형태로 나누고 싶을 때..(두 테이블간의 데이터는 동기화)
1). TEST TABLE & DATA 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
-- ST_TABLE 생성 SQL> DROP TABLE ST_TABLE; DROP TABLE ST_TABLE ORA-00942: table or view does not exist SQL> CREATE TABLE ST_TABLE AS 2 SELECT LEVEL l_no 3 ,LENGTH(LEVEL) CNT 4 ,TO_DATE('0001-01-01', 'YYYY-MM-DD') + LEVEL - 1 CHILD_DATE 5 FROM DUAL 6 CONNECT BY LEVEL <= 100; Table created -- BACKUP TABLE 생성 SQL> DROP TABLE BU_TABLE; DROP TABLE BU_TABLE ORA-00942: table or view does not exist SQL> CREATE TABLE BU_TABLE AS 2 SELECT * 3 FROM ST_TABLE 4 ; Table created |
================================================================
– Test Case
– Case1) TRIGGER안에 COMMIT이 없을 경우
– Case2) TRIGGER안에 COMMIT이 있을 경우
– Case3) TRIGGER안에 PRAGMA AUTONOMOUS_TRANSACTION 선언 한 경우
================================================================
Case1) TRIGGER안에 COMMIT이 없을 경우
2-1). TRIGGER생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
-- TO_BU_TABLE_TRIGGER 생성(ST_TABLE에서 DML작업이 발생 되었을 때, BU_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_BU_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON ST_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO BU_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_BU_TABLE_TRIGGER; -- TO_ST_TABLE_TRIGGER 생성(BU_TABLE에서 DML작업이 발생 되었을 때, ST_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_ST_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON BU_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO ST_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_ST_TABLE_TRIGGER; |
3). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
-- 삽입 할 데이터 존재여부 확인 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- ROLLBACK 일 경우 SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (102,2,SYSDATE); SQLERROR = ORA-04091: table GSFSTUNE.ST_TABLE is mutating, trigger/function may not see it 1 row inserted -- 데이터 화인 SQL> SELECT * 2 FROM ST_TABLE 3 WHERE L_NO = 102 4 ; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-22 SQL> SELECT * 2 FROM BU_TABLE 3 WHERE L_NO = 102 4 ; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-22 SQL> ROLLBACK; Rollback complete SQL> SELECT * FROM ST_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- COMMIT 일 경우 SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (102,2,SYSDATE); SQLERROR = ORA-04091: table GSFSTUNE.ST_TABLE is mutating, trigger/function may not see it 1 row inserted SQL> COMMIT; Commit complete SQL> SELECT * 2 FROM ST_TABLE 3 WHERE L_NO = 102 4 ; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-22 SQL> SELECT * 2 FROM BU_TABLE 3 WHERE L_NO = 102 4 ; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-22 |
4). 테스트 결과
: 데이터는 정상적으로 Insert는 되지만 ORA-04091 에러가 발생이 됩니다.
Case2) TRIGGER안에 COMMIT이 있을 경우
2-2). TRIGGER생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
-- TO_BU_TABLE_TRIGGER 생성(ST_TABLE에서 DML작업이 발생 되었을 때, BU_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_BU_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON ST_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO BU_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); COMMIT; <-- COMMIT 선언!! END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_BU_TABLE_TRIGGER; -- TO_ST_TABLE_TRIGGER 생성(BU_TABLE에서 DML작업이 발생 되었을 때, ST_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_ST_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON BU_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO ST_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); COMMIT; <-- COMMIT 선언!! END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_ST_TABLE_TRIGGER; |
3). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
-- 삽입 할 데이터 존재여부 확인 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- ROLLBACK 일 경우 SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (102,2,SYSDATE); SQLERROR = ORA-04091: table GSFSTUNE.ST_TABLE is mutating, trigger/function may not see it SQLERROR = ORA-04092: cannot COMMIT in a trigger <-- COMMIT을 할 수 없다는 에러 메세지 1 row inserted -- 데이터 확인 SQL> SELECT * FROM ST_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 SQL> SQL> ROLLBACK; Rollback complete SQL> SELECT * FROM ST_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- -- COMMIT 일 경우 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (102,2,SYSDATE); SQLERROR = ORA-04091: table GSFSTUNE.ST_TABLE is mutating, trigger/function may not see it SQLERROR = ORA-04092: cannot COMMIT in a trigger <-- COMMIT을 할 수 없다는 에러 메세지 1 row inserted SQL> SELECT * FROM ST_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 SQL> COMMIT; Commit complete SQL> SELECT * FROM ST_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 102; L_NO CNT CHILD_DATE ---------- ---------- ----------- 102 2 2015-01-26 |
4). 테스트 결과
: TRIGGER안에 Commit을 선언 해 봤자 소용이 없는 것을 확인 할 수 있습니다.
결론적으로 TRIGGER안에는 COMMIT/ROLLBACK과 같이 DML 제어문은 사용 할 수가 없습니다.
Case3) TRIGGER안에 PRAGMA AUTONOMOUS_TRANSACTION 선언 한 경우
[참고] PARGMA AUTONOMOUS_TRANSACTION 옵션의 의미
: PKG 컴파일 시, DML작업이 오래 걸릴 경우, 작업이 끝날때 까지 기다리지 말고 다음 라인으로 넘어가라는
명령어
2-3). TRIGGER생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
-- TO_BU_TABLE_TRIGGER 생성(ST_TABLE에서 DML작업이 발생 되었을 때, BU_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_BU_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON ST_TABLE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; <-- PRAGMA AUTONOMOUS_TRANSACTION 옵션 선언!! BEGIN IF inserting THEN INSERT INTO BU_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_BU_TABLE_TRIGGER; -- TO_ST_TABLE_TRIGGER 생성(BU_TABLE에서 DML작업이 발생 되었을 때, ST_TABLE로 TRIGGER발생) CREATE OR REPLACE TRIGGER TO_ST_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON BU_TABLE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; <-- PRAGMA AUTONOMOUS_TRANSACTION 옵션 선언!! BEGIN IF inserting THEN INSERT INTO ST_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERROR = '||SQLERRM); END TO_ST_TABLE_TRIGGER; |
3). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 |
-- 삽입 할 데이터 존재여부 확인 SQL> SELECT * FROM BU_TABLE WHERE L_NO = 103; L_NO CNT CHILD_DATE ---------- ---------- ----------- SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (103,3,SYSDATE); SQLERROR = Oracle error 00000 SQLERROR = ORA-00036: maximum number of recursive SQL levels (50) exceeded 1 row inserted SQL> SELECT * FROM ST_TABLE WHERE L_NO = 103; L_NO CNT CHILD_DATE ---------- ---------- ----------- 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 26 rows selected SQL> SELECT * FROM BU_TABLE WHERE L_NO = 103; L_NO CNT CHILD_DATE ---------- ---------- ----------- 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 25 rows selected SQL> ROLLBACK; Rollback complete SQL> SELECT * FROM ST_TABLE WHERE L_NO = 103; L_NO CNT CHILD_DATE ---------- ---------- ----------- 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 26 rows selected SQL> SELECT * FROM BU_TABLE WHERE L_NO = 103; L_NO CNT CHILD_DATE ---------- ---------- ----------- 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 103 3 2015-01-26 25 rows selected |
4). 테스트 결과
: PRAGMA AUTONOMOUS_TRANSACTION 옵션으로 인해 ST_TABLE DML작업 후 BU_TABLE DML
작업을 하고 또 다시 트리거를 만나 ST_TABLE, BU_TABLE DML 작업을 계속적으로 하다가..
오라클에서 Setting되어 있는 일정 라인수를 만나게 되면서 Exception에 빠지게 되었고
그러면서 에러를 발생 시켰습니다.
만일 L_NO가 Unique Key값이 였다면 2번째 Looping 돌때 Unique 에러를 발생 시켰을 것입니다.
위 예제는 L_NO가 Unique Key Column으로 생성을 안했기 때문에 계속적으로 DML작업을 하고 있는
것을 확인 할 수가 있습니다.
위 3가지 예제에서 볼 수 있듯이 우리가 원하는 값은 나오지만 에러메세지는 계속적으로 발생이 되고 있습니다.
에러가 발생되는 프로그램을 주기적으로 사용 할 수가 없기 때문에 에러를 해결해야 되는데요.
해결방안은 여러 방법이 있지만 먼저 첫번째 방안으로 PKG를 사용하여 TEMP테이블에 담아 놓는 방법이 있습니다.
예제를 통해 알아 보도록 하겠습니다.
1). TEMP TABLE 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> DROP TABLE TARGET_T_TEMP; Table dropped SQL> CREATE TABLE TARGET_T_TEMP 2 (L_NO NUMBER 3 ,CNT NUMBER 4 ,CHILD_DATE DATE 5 ,LAST_UPDATE_DATE DATE -- TARGET_MST TABLE에 수행날짜와 비교 될 날짜 6 ,TABLE_FLAG VARCHAR2(20) -- TARGET / BACKUP 7 ,TRIGGER_FLAG VARCHAR2(20) -- INSERT/UPDATE/DELETE 8 ) 9 ; Table created |
2). TRIGGER 재 생성
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
-- TO BACKUP_TABLE TRIGGER CREATE OR REPLACE TRIGGER TO_BU_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON ST_TABLE FOR EACH ROW DECLARE v_cnt NUMBER; BEGIN IF inserting THEN -- INSERT 발생 시 SELECT COUNT(1) cnt INTO v_cnt FROM BU_TABLE a WHERE a.l_no = :new.l_no ; IF v_cnt = 0 THEN INSERT INTO BU_TABLE (L_NO ,CNT ,CHILD_DATE ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ); ELSE NULL; END IF; END IF; IF updating THEN -- UPDATE 발생 시 UPDATE BU_TABLE A SET CNT = :new.cnt ,CHILD_DATE = :new.child_date WHERE L_NO = :new.l_no; END IF; IF deleting THEN -- DELETE 발생 시 DELETE BU_TABLE WHERE L_NO = :new.l_no; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERR = '||SQLERRM); END TO_BU_TABLE_TRIGGER; -- TO STANDARD_TABLE TRIGGER CREATE OR REPLACE TRIGGER TO_ST_TABLE_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON BU_TABLE FOR EACH ROW DECLARE BEGIN IF inserting THEN INSERT INTO TARGET_T_TEMP (L_NO ,CNT ,CHILD_DATE ,LAST_UPDATE_DATE ,TABLE_FLAG ,TRIGGER_FLAG ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ,SYSDATE ,'TARGET' ,'INSERT' ); END IF; IF updating THEN INSERT INTO TARGET_T_TEMP (L_NO ,CNT ,CHILD_DATE ,LAST_UPDATE_DATE ,TABLE_FLAG ,TRIGGER_FLAG ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ,SYSDATE ,'TARGET' ,'UPDATE' ); END IF; IF deleting THEN INSERT INTO TARGET_T_TEMP (L_NO ,CNT ,CHILD_DATE ,LAST_UPDATE_DATE ,TABLE_FLAG ,TRIGGER_FLAG ) VALUES (:new.l_no ,:new.cnt ,:new.child_date ,SYSDATE ,'TARGET' ,'DELETE' ); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('SQLERR = '||SQLERRM); END TO_ST_TABLE_TRIGGER; |
3). PKG 생성(DBMS_JOB 에 등록 될 Package)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 |
CREATE OR REPLACE PACKAGE BODY TABLE_SYNC_PKG IS PROCEDURE put_log(p_log IN VARCHAR2) IS v_sysdate VARCHAR2(100); BEGIN IF p_log IS NULL THEN RETURN; END IF; v_sysdate := ' ( '||to_char(SYSDATE,'YYYYMMDD HH24:MI:SS')||' )'; dbms_output.put_line(p_log||v_sysdate); EXCEPTION WHEN OTHERS THEN RETURN; END put_log; PROCEDURE excution_data_proc IS CURSOR insert_sql_cur IS SELECT l_no ,cnt ,child_date ,last_update_date ,table_flag ,trigger_flag FROM target_t_temp WHERE trigger_flag = 'INSERT' ; CURSOR update_sql_cur IS SELECT l_no ,cnt ,child_date ,last_update_date ,table_flag ,trigger_flag FROM target_t_temp WHERE trigger_flag = 'UPDATE' ; CURSOR delete_sql_cur IS SELECT l_no ,cnt ,child_date ,last_update_date ,table_flag ,trigger_flag FROM target_t_temp WHERE trigger_flag = 'DELETE' ; v_seq_no DATE := NULL; v_cnt NUMBER := 0; v_cnt_1 NUMBER := 0; BEGIN put_log('Copy excution_data_proc start...'); v_seq_no := sysdate; put_log('Data Insert Start...'); FOR i_sql_rec IN insert_sql_cur LOOP BEGIN SELECT COUNT(1) INTO v_cnt FROM st_table a WHERE a.l_no = i_sql_rec.l_no ; EXCEPTION WHEN no_data_found THEN NULL; WHEN OTHERS THEN put_log('[INSERT_SQL_ERROR...] '||SQLERRM); END; IF v_cnt = 0 THEN BEGIN INSERT INTO st_table a (l_no ,cnt ,child_date) VALUES (i_sql_rec.l_no ,i_sql_rec.cnt ,i_sql_rec.child_date); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; put_log('[INSERT_ERROR...] '||SQLERRM); END; END IF; END LOOP; put_log('Data Update Start...'); FOR u_sql_rec IN update_sql_cur LOOP BEGIN SELECT COUNT(1) INTO v_cnt FROM st_table a WHERE a.l_no = u_sql_rec.l_no ; EXCEPTION WHEN no_data_found THEN NULL; WHEN OTHERS THEN put_log('[UPDATE_SQL_ERROR...] '||SQLERRM); END; BEGIN SELECT COUNT(1) INTO v_cnt_1 FROM st_table a WHERE a.l_no = u_sql_rec.l_no AND a.cnt = u_sql_rec.cnt AND a.child_date = u_sql_rec.child_date ; EXCEPTION WHEN no_data_found THEN NULL; WHEN OTHERS THEN put_log('[UPDATE_DEDAIL_SQL_ERROR...] '||SQLERRM); END; IF v_cnt = 1 AND v_cnt_1 = 0 THEN BEGIN UPDATE st_table a SET a.cnt = u_sql_rec.cnt ,a.child_date = u_sql_rec.child_date WHERE a.l_no = u_sql_rec.l_no ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; put_log('[UPDATE_ERROR...] '||SQLERRM); END; ELSIF v_cnt = 0 THEN put_log('[UPDATE_NO_DATA_FOUND]'); END IF; END LOOP; put_log('Data Delete Start...'); FOR d_sql_rec IN delete_sql_cur LOOP BEGIN SELECT COUNT(1) INTO v_cnt FROM st_table a WHERE a.l_no = d_sql_rec.l_no ; EXCEPTION WHEN no_data_found THEN NULL; WHEN OTHERS THEN put_log('[DELETE_SQL_ERROR...] '||SQLERRM); END; IF v_cnt = 1 THEN BEGIN DELETE st_table WHERE l_no = d_sql_rec.l_no ; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; put_log('[DELETE_ERROR...] '||SQLERRM); END; END IF; END LOOP; BEGIN DELETE target_t_temp; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; put_log('[UPDATE_ERROR...] '||SQLERRM); END; END excution_data_proc; END TABLE_SYNC_PKG; |
===========================================================
– Test Case
– Case1) ST_TABLE INSERT
– Case2) BU_TABLE INSERT
– Case3) ST_TABLE UPDATE
– Case4) BU_TABLE UPDATE
– Case5) ST_TABLE DELETE
– Case6) BU_TABLE DELETE
===========================================================
Case1) ST_TABLE INSERT
1-1). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- 삽입 할 데이터 존재여부 확인 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO ST_TABLE(L_NO, CNT, CHILD_DATE) VALUES (106,6,SYSDATE); 1 row inserted <-- 에러없이 수행. SQL> COMMIT; Commit complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 6 BU_TABLE 106 6 TEMP_TABLE 106 6 SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 6 BU_TABLE 106 6 TEMP_TABLE <-- 수행 후 삭제!! |
2). 테스트 결과
: PKG를 보시면 ST_TABLE에 Insert 하고 하는 Data가 있으면 Skip처리..
Data가 없으면 DML작업을 수행 하도록 Validation Check 로직으로 인해 Insert가 안됨.
Case2) BU_TABLE INSERT
1-2). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- SQL> SET SERVEROUTPUT ON; SQL> INSERT INTO BU_TABLE(L_NO, CNT, CHILD_DATE) VALUES (107,7,SYSDATE); 1 row inserted <-- 에러없이 수행. SQL> COMMIT; Commit complete SQL> SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE <-- 데이터 없음!! BU_TABLE 107 7 TEMP_TABLE 107 7 <-- 데이터 있음!! SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 <-- 데이터 삽입!! BU_TABLE 107 7 TEMP_TABLE <-- 수행 후 삭제!! |
2). 테스트 결과
: PKG가 돌면서..
ST_TABLE에 L_NO = 107 데이터가 없기 때문에 Insert 작업이 수행 된 것을 확인 할 수 있습니다.
Case3) ST_TABLE UPDATE
1-3). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
-- UPDATE할 데이타 존재여부 확인 후 UPDATE SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 6 BU_TABLE 106 6 TEMP_TABLE -- CNT = 6 --> CNT = 3 으로 변경!! SQL> SET SERVEROUTPUT ON; -- ROLLBACK일 경우 SQL> UPDATE ST_TABLE A 2 SET CNT = 3 3 WHERE L_NO = 106 4 ; 1 row updated <-- 에러없이 수행!! SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 3 <-- 변경!! BU_TABLE 106 3 <-- 변경!! TEMP_TABLE 106 3 <-- 삽입!! SQL> ROLLBACK; Rollback complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 6 <-- 복구 BU_TABLE 106 6 <-- 복구 TEMP_TABLE <-- 복구 SQL> SET SERVEROUTPUT ON; -- COMMIT일 경우 SQL> UPDATE ST_TABLE A 2 SET CNT = 3 3 WHERE L_NO = 106 4 ; 1 row updated SQL> COMMIT; Commit complete SQL> SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 3 <-- 변경!! BU_TABLE 106 3 <-- 변경!! TEMP_TABLE 106 3 <-- 삽입!! SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 106 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 106 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 106; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 106 3 BU_TABLE 106 3 TEMP_TABLE <-- 수행 후 삭제!! |
2). 테스트 결과
: 이번 예제 또한 Validation Check로 인해 데이터는 Insert가 안 되었습니다.
Case4) BU_TABLE UPDATE
1-4). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
-- UPDATE할 데이타 존재여부 확인 후 UPDATE SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE 107 7 TEMP_TABLE -- CNT = 7를 CNT = 4로 변경!! SQL> SET SERVEROUTPUT ON; -- ROLLBACK일 경우 SQL> UPDATE BU_TABLE A 2 SET CNT = 4 3 WHERE L_NO = 107 4 ; 1 row updated SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE 107 4 <-- 변경!! TEMP_TABLE 107 4 <-- 삽입!! SQL> ROLLBACK; Rollback complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 <-- 복구!! BU_TABLE 107 7 <-- 복구!! TEMP_TABLE <-- 복구!! -- COMMIT일 경우 SQL> UPDATE BU_TABLE A 2 SET CNT = 4 3 WHERE L_NO = 107 4 ; 1 row updated SQL> COMMIT; Commit complete SQL> SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 <-- 변경 안됨!! BU_TABLE 107 4 <-- 변경!! TEMP_TABLE 107 4 <-- 삽입!! SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 4 <-- 변경!! BU_TABLE 107 4 <-- 변경!! TEMP_TABLE <-- 실행 후 삭제!! |
2). 테스트 결과
: 패키지 실행 후 데이터 Update 됨!!
Case5) ST_TABLE DELETE
1-5). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 |
-- DELETE할 데이타 존재여부 확인 후 DELETE SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE 107 7 TEMP_TABLE SQL> SET SERVEROUTPUT ON; -- ROLLBACK일 경우 SQL> DELETE ST_TABLE 2 WHERE L_NO = 107; 1 row deleted SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE <-- 삭제!! BU_TABLE <-- 삭제!! TEMP_TABLE 107 7 <-- BU_TABLE의 TRIGGER로 인한 삭제된 데이터 삽입!! SQL> ROLLBACK; Rollback complete SQL> SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 <-- 복구!! BU_TABLE 107 7 <-- 복구!! TEMP_TABLE <-- 복구!! SQL> SET SERVEROUTPUT ON; -- COMMIT일 경우 SQL> DELETE ST_TABLE 2 WHERE L_NO = 107; 1 row deleted SQL> COMMIT; Commit complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE <-- 삭제!! BU_TABLE <-- 삭제!! TEMP_TABLE 107 7 <-- BU_TABLE의 TRIGGER로 인한 삭제된 데이터 삽입!! SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE BU_TABLE TEMP_TABLE |
2). 테스트 결과
: ST_TABLE이 Delete를 만나면서 BU_TABLE도 Delete가 되고 그로 인해 TRIGGER가 발생되어
TARGET_T_TEMP테이블에 삭제 된 데이터를 삽입했다가 ST_TABLE에 데이터가 있으면 Skip..
ST_TABLE에 데이터가 없으면 삭제!! 되는 것을 확인 할 수 있습니다.
Case6) BU_TABLE DELETE
1-6). 데이터 삽입 후 ROLLBACK / COMMIT일 경우 데이터가 어떻게 들어 가는지 확인!!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
-- DELETE할 데이타 존재여부 확인 후 DELETE SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE 107 7 TEMP_TABLE SQL> SET SERVEROUTPUT ON; -- ROLLBACK일 경우 SQL> DELETE BU_TABLE 2 WHERE L_NO = 107; 1 row deleted SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE <-- 삭제!! TEMP_TABLE 107 7 <-- TRIGGER로 인한 삭제 된 데이터 삽입!! SQL> ROLLBACK; Rollback complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 <-- 복구!! BU_TABLE 107 7 <-- 복구!! TEMP_TABLE <-- 복구!! SQL> SET SERVEROUTPUT ON; -- COMMIT일 경우 SQL> DELETE BU_TABLE 2 WHERE L_NO = 107; 1 row deleted SQL> COMMIT; Commit complete SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE 107 7 BU_TABLE <-- 삭제!! TEMP_TABLE 107 7 <-- TRIGGER로 인한 삭제 된 데이터 삽입!! SQL> TABLE_SYNC_PKG 실행 SQL> SELECT 'ST_TABLE' TABLE_NAME, L_NO, CNT FROM ST_TABLE WHERE L_NO = 107 2 UNION ALL 3 SELECT 'BU_TABLE' TABLE_NAME, L_NO, CNT FROM BU_TABLE WHERE L_NO = 107 4 UNION ALL 5 SELECT 'TEMP_TABLE' TABLE_NAME, L_NO, CNT FROM TARGET_T_TEMP WHERE L_NO = 107; TABLE_NAME L_NO CNT ---------- ---------- ---------- ST_TABLE BU_TABLE TEMP_TABLE |
2). 테스트 결과
: ST_TABLE이 Delete를 만나면서 BU_TABLE도 Delete가 되고 그로인해 TRIGGER가 발생되어
TARGET_T_TEMP테이블에 삭제 된 데이터를 삽입했다가 ST_TABLE에 데이터가 있으면 Skip..
ST_TABLE에 데이터가 없으면 삭제!! 되는 것을 확인 할 수 있습니다.
5. 결론
| * TRIGGER는 단방향성으로 사용을 할 때는 효과적으로 사용 할 수가 있지만 양방향성으로 사용 하게 되었을때는 여러 에러를 발생 시키는 것을 확인 할 수가 있었습니다. * 에러를 해결하기 위한 방법으로 PKG와 TEMP TABLE을 활용하여 에러를 해결 할 수 있는 것을 * TRIGGER는 양방향보다는 단방향성으로 사용 할 때 효과적인 것을 확인 할 수가 있습니다. * 하기 URL은 오라클 클럽의 마농님께서 저와 다른 방법으로 트리거를 구성해 놓은 소스 입니다. |
About the Author
View all posts by 김지룡● (주)더비소프트 SI&ITO사업부 / ERP사업팀 팀원
● 경력 5년(현 LG전자 GERP DBA/Tuning 업무)
● 경력 5년(현 LG전자 GERP DBA/Tuning 업무)
Related Posts
- [DB 칼럼] Pivot queries (11g New Feature) 14-09-15 일반태그: Application Architecture. DB태그: Oracle.
- [DB 칼럼] LISTAGG Function (11g New Feature) 14-09-15 일반태그: Application Architecture. DB태그: Oracle.
하대리.
트리거 관련 데이터 동기화 테스트 하느라 고생했다.
처음 칼럼 등록한거 맞아? 너무 잘 올렸는데? ^^
앞으로 좋은 칼럼 많이 부탁한다!