Advenced Oracle Trigger (오라클 트리거 심화)
오라클 트리거에서 포인트 히스토리로 현재 포인트를 관리하는 방법을 알아보자!
우선 간단하게 게시판 글의 점수주기 부분을 예를 들어 보기로 하겠다.
아래 테이블 3개는 게시판 데이터와 포인트 데이터, 그리고 히스토리를
정규화 하여 초간략한 게시판 형태의 스키마를 예를 들기로 하였다.
인덱스, 제약조건, 체크제약, 디폴트 값등 부가적인 조건들은
전부 생략할 것이며 핵심적인 부분은 얘기하고자 한다.
/*
게시물 데이터 (샘플)
DataCD : 글 번호(Primary Key)
Subject : 제목
ContentText : 글 내용
RegDate : 생성일
*/
CREATE TABLE BBS_DATA_INFO(
DataCD NUMBER Primary Key
, Subject Varchar2(255) not null
, ContentText Long not null
, RegDate DATE Default SysDATE
);
/*
포인트 히스토리 (샘플)
PHCD : Primary Key
DataCD : 글 번호(BBS_DATA_INFO의 DataCD의 참조키 : 제약조건 설정 생략 예정)
NID : 회원 아이디(이중 투표를 방지하기 위해서.. : 회원 아이디 부분이라고 생각해 주길..)
Points : 점수
RegDate : 생성일
*/
CREATE TABLE BBS_POINT_HISTORY (
PHCD NUMBER PRIMARY KEY
, DataCD NUMBER NULL
, NID NUMBER NULL
, Points NUMBER DEFAULT 0
, RegDate DATE DEFAULT SYSDATE
);
/*
포인트 히스토리 (샘플)
DPCD : Primary Key
DataCD : 글 번호(BBS_DATA_INFO의 DataCD의 참조키 : 제약조건 설정 생략 예정)
PointCnt : 투표자 수
PointTotal : 점수 합계
RegDate : 생성일
*/
CREATE TABLE BBS_DATA_POINT (
DPCD NUMBER PRIMARY KEY
, DataCD NUMBER NOT NULL
, PointCnt NUMBER NOT NULL
, PointTotal NUMBER NOT NULL
, RegDate DATE DEFAULT SYSDATE
);
-- 이상 없이 생성되었는지 한번 쿼리해 본다.
SELECT * FROM BBS_POINT_HISTORY;
SELECT * FROM BBS_DATA_POINT;
/*
자 이상 없다면 트리거를 어떤 형태로 걸지 설명하겠다.
우선 글에 대해서 로그인 해 있는 유저가 점수주기 버튼(예 1점,2점,3점,4점,... 등)을
클릭하면 점수를 업데이트 해 주고 로그를 쌓으면 된다.
아마도 쿼리는 다음과 같을 것이다.
해당 글에서 처음 점수주기가 실행되는 경우
- Insert Into BBS_DATA_POINT(DPCD, DATACD, PointCnt, PointTotal)
Values(시퀀스, 글 번호, 투표자수(1), 점수);
처음이 아닌 경우
- Update BBS_DATA_POINT
SET PointCnt = PointCnt + 1
, PointTotal = PointTotal + 점수
where DATACD = v_DATACD
관리툴 OR DBMS에 의해서 점수가 조작 경우 (UPDATE OR DELETE 되는 경우)
- Update BBS_DATA_POINT
SET PointCnt = PointCnt (+ or -) 1
, PointTotal = PointTotal + 점수
where DATACD = v_DATACD
위 3가지 동작을 할때마다 로그가 쌓인다.
Insert into BBS_Point_History(.......) Values(........);
이런 부분을 프로그램 하다 보면 로그라는 것을 일관성 있게 유지하는 것이
너무 힘든 것 처럼 보인다.
저런 부분을 비지니스 로직에 넣으면 현재 개발자 이외 다음 개발자정도에서
로그에 대한 구멍은 생기기 마련이다.(사람이기 때문에..)
그래서 테이블에 이벤트를 걸어 트리거를 발생시킨다.
여타 DB에서 트리거는 성능상 좋지 않다, 뭐 그런 얘기를 하는 분들도 계신데.
오라클은 전혀 그렇지 않다.(오라클 인덱스 부분을 잘 살펴보면 알수 있다.)
그리고 비지니스로직에서 저런 부분이 빠진다면 유지 보수도 훨씬 쉬워질 것이다.
자 위에서 설명한 방식으로 하면 BBS_DATA_POINT에 변경이 있으면
늘 BBS_Point_History로 insert를 하면 되는 흔해빠진 트리거 예문이 된다.
이번에 할 것은 이와 반대로 처리해 보는 것을 하겠다.
BBS_DATA_Point(Insert or Update) --> BBS_Point_History(Insert) 가 아닌
BBS_Point_History(Insert) --> BBS_DATA_POINT (Insert or Update) 이다.
아래 부분은 이를 가능케 하는 트리거 예문이다.
*/
CREATE OR REPLACE TRIGGER JAPANDEV.TRI_BBS_POINT_HISTORY_TT_LOG
AFTER INSERT OR UPDATE OR DELETE
ON BBS_POINT_HISTORY_TT FOR EACH ROW
DECLARE
V_PSUM NUMBER := 0; --점수 합계
V_PCNT NUMBER := 0; --투표자 수
V_COUNT NUMBER := 0; --해당글에 대한 점수주기가 처음인지 아닌지 체크 변수
BEGIN
IF INSERTING THEN
SELECT Count(DPCD) INTO V_COUNT FROM BBS_DATA_POINT WHERE DataCD = :NEW.DataCD;
IF V_COUNT = 0 THEN --처음이면
INSERT INTO BBS_DATA_POINT(DPCD, DATACD, POINTCNT, POINTTOTAL) VALUES(:NEW.PHCD, :NEW.DataCD, 0, 0);
END IF;
V_PCNT := 1;
V_PSUM := :NEW.POINTS;
UPDATE BBS_DATA_POINT
SET POINTTOTAL = POINTTOTAL + (V_PSUM)
, POINTCNT = POINTCNT + (V_PCNT)
WHERE DataCD = :NEW.DataCD;
ELSIF UPDATING THEN
V_PCNT := 0;
V_PSUM := :NEW.POINTS-:OLD.POINTS;
UPDATE BBS_DATA_POINT
SET POINTTOTAL = POINTTOTAL + (V_PSUM)
, POINTCNT = POINTCNT + (V_PCNT)
WHERE DataCD = :OLD.DataCD;
ELSE
V_PCNT := -1;
V_PSUM := 0-:OLD.POINTS;
UPDATE BBS_DATA_POINT
SET POINTTOTAL = POINTTOTAL + (V_PSUM)
, POINTCNT = POINTCNT + (V_PCNT)
WHERE DataCD = :OLD.DataCD;
END IF;
END;
/*
위 부분에서 :NEW, :OLD가 뭔지 모르시는 분은 새 탭으로 여기로 이동
[ORACLE]오라클에서 시퀀스(SEQUENCE) 처리를 트리거(TRIGGER)로 생성 by VINS
자 이제 위 데이터가 정상적으로 작동 되는지 살펴 볼 것이다.
아래 준비된 쿼리 8개를 하나씩 실행해 보면서 BBS_DATA_POINT 테이블의
데이터 변화를 살펴 보자 (투표자 수와 점수 합계가 제대로 유지 되고 있는지 등...)
*/
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(1,1001,111111111,7);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(2,1001,222222222,8);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(3,1001,333333333,3);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(4,1001,444444444,4);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(5,1001,555555555,8);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(6,1001,666666666,2);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(7,1001,777777777,4);
INSERT INTO BBS_POINT_HISTORY(PHCD, DATACD, NID, POINTS) VALUES(8,1001,888888888,6);
COMMIT;
/*
위 8개를 실행하면 다음과 같다.
SUM : 42, COUNT : 8
*/
UPDATE BBS_POINT_HISTORY SET POINTS = 0 WHERE PHCD = 8;
COMMIT;
/*
PHCD : 8 IS POINTS : 6 --> 0 Updated
SUM : 36, COUNT : 8
*/
DELETE FROM BBS_POINT_HISTORY WHERE PHCD = 7;
COMMIT;
/*
PHCD : 7 IS POINTS : 4 --> 0 Deleted
SUM : 36, COUNT : 8
데이터가 일관성 있게 유지 되어지는 것이 보여 질 것이다.
이때 트랜젝션을 좀 하는 분은 이런 딴지를 걸지도 모르겠다.
트리거 실행도중 에러가 나면 일관성이 깨지지 않느냐고..
오라클에서 트리거는 트리거를 발생시키는 부분과 트랜젝션을 공유한다.
즉 기본값이 종속적인 트랜젝션에 포함되기 때문에 트리거가 실패할 경우 점수 주기도 롤백된다.
종속적이지 않게 독립 트랜젝션으로 동작하도록 설정하는 것도 가능하나 이 강좌의 범위를 넘어가기 때문에
필자의 블로그 ORACLE TRANSACTION 부분을 검색해 보길 바란다. (위 검색바에서....)
*/
BBS_POINT_HISTORY 는 늘 로우 단위로 데이터를 유지하게 되고
BBS_DATA_POINT 는 글번호와 1:1 관계로 데이터가 유지 된다.
이때 PointCnt와, PointTotal은 다음 쿼리와 항상 동일한 데이터가 유지 되어야 한다.
SELECT count(*), sum(points) FROM BBS_POINT_HISTORY Where DataCD = 해당글
이로 오라클 트리거 심화 강좌(Advenced Oracle Trigger)를 마치도록 하겠다. bye~~
