일을 하다보면 다른 소유자(계정)간에 협업을 할 때 데이터 제공 등의 목적으로 View를 생성, 제공해 주는 경우가 종종있습니다. 그리고 애써 View를 구성해서 제공해 주었는데 부득이하게 등의 이유로 제공받은 쪽에서 데이터를 업데이트를 해야하는 경우가 있습니다. 이때 원본 테이블을 제공해 줄 수 없는 경우 또는 원본 테이블이 여러개인 경우(대부분 View를 생성하는 경우가 원본이 여러개인 경우일 듯 합니다...) 등의 이유로 난감한 경우가 발생하기도 합니다. (제 경우에는 늘어나는 업무량에 '그냥 확 원본 테이블을 제공하자!'가 항상 머릿속을 맴돌지만, 원본 테이블들을 제공하고 업데이트 시 데이터/업무 흐름이나 컬럼 플래그 케이스 등을 설명하고 문서를 작성하는게 업무량은 훨씬 더 많이 늘어나기에... 포기했습니다.)
이런 난감한 경우에 View에 Trigger를 연결해서 원본 테이블의 데이터를 업데이트 하도록 할 수 있습니다. 즉, 데이터/업무 흐름등에 맞춰 Trigger를 생성하고 View에 연결하면, View를 제공받는 쪽에 원본 테이블을 알려 줄 필요없이 View에 Update를 실행하는 것만으로도 원본 테이블에 대한 데이터를 수정할 수 있게 됩니다. 실은 저도 View에 업데이트 구문이 실행된다는 사실은 이번에 알게 됬습니다. Trigger 없이 실행한 경우 실행만 되고 실제 원본 데이터가 업데이트 되진 않더군요.
'업데이트 요청 및 백업 용'같은 성격의 테이블을 만들어 이력을 남기도록 Trigger에 함께 추가 해 놓으니 협업할 때 검증 용으로 괜찮았습니다.
아래는 간단하게 테스트 해 볼 수 있도록 한 예제입니다.
아참, 제공받는 소유자(계정)에 대해서 View를 Update할 수 있는 권한을 부여해 주어야 한다는 것을 잊지 마세요.
1. 원본 테이블(Origin Table) 및 View의 생성
View를 업데이트 할 때 수정 될 원본 테이블(Origin Table)과 View를 생성하고, 원본 테이블(Origin Table)의 데이터를 넣어 줍니다.
-- Origin Table을 생성합니다.
CREATE TABLE ORIGIN_TABLE (
SEQ NUMBER
, FLAG CHAR(1)
);
-- Origin Table을 기준으로 하는 View Table을 생성합니다.
CREATE VIEW ORIGIN_DATA_VIEW (
SEQ
, FLAG
) AS
SELECT SEQ, FLAG FROM ORIGIN_TABLE;
-- 이 테이블은 원본에 대한 백업과 요청 이력을 남겨 보려 생성하는 테이블 입니다.
CREATE TABLE REQ_HISTORY (
SEQ NUMBER
, FLAG CHAR(1)
, REQ_DATE DATE
);
-- 원본 테이블(Origin Table)에 데이터를 넣어 줍니다.
INSERT INTO ORIGIN_TABLE (SEQ, FLAG) VALUES ( 1, 'S' );
INSERT INTO ORIGIN_TABLE (SEQ, FLAG) VALUES ( 2, 'S' );
INSERT INTO ORIGIN_TABLE (SEQ, FLAG) VALUES ( 3, 'S' );
INSERT INTO ORIGIN_TABLE (SEQ, FLAG) VALUES ( 4, 'S' );
2. View에 연결되어 작동하는 Trigger 생성
이제 View에 연결되어 작동하는 Trigger를 생성합니다. 일반 Trigger 생성과 같이 Insert, Update, Delete에 대한 처리를 해 줄 수 있습니다만 여기서는 Update만 하도록 하겠습니다.
-- Update시 작동하는 트리거를 작성합니다.
CREATE OR REPLACE TRIGGER TRI_UPDATE_VIEW
INSTEAD OF UPDATE --(OR INSERT OR DELETE) 여기 INSTEAD가 중요합니다.
ON ORIGIN_DATA_VIEW
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
-- 원본 테이블을 업데이트 합니다.
UPDATE ORIGIN_TABLE SET FLAG = :NEW.flag WHERE SEQ = :NEW.seq;
-- 원본 백업 및 이력을 남겨 봅니다.
INSERT INTO REQ_HISTORY(SEQ, FLAG, REQ_DATE) VALUES (:NEW.seq, :OLD.flag, SYSDATE);
EXCEPTION
WHEN OTHERS THEN RAISE;
END TRI_UPDATE_VIEW;
3. 작동에 대한 테스트
이제 실제로 View에 업데이트를 실행 해서 원본 테이블(Origin Table)의 데이터가 변경 되었는지 확인해 봅니다.
-- VIEW를 UPDATE 해 봅니다.
UPDATE ORIGIN_DATA_VIEW SET FLAG = 'R' WHERE SEQ = 1;
UPDATE ORIGIN_DATA_VIEW SET FLAG = 'X' WHERE SEQ = 1;
-- 갱신이 되었는지 원본 테이블(Origin Table)과 VIEW를 조회 해 봅니다.
SELECT * FROM ORIGIN_TABLE;
SELECT * FROM ORIGIN_DATA_VIEW;
