DB Oracle 트리거(Trigger) 생성을 통한 데이터 관리하는 방법

DB Oracle 트리거(Trigger) 생성을 통한 데이터 관리하는 방법

트리거(Trigger)란?
1) 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미
2) 테이블에 INSERT 나 UPDATE 또는 DELETE 작업이 발생되면 자동으로 실행되는 코드
3) 트리거의 주요 목적은 테이블의 데이터 무결성 보장과 함께 데이터베이스 관리의 자동화가 장점

▶ SHOP 스키마(유저)에서 MEMBERTBL 테이블을 조회했을 때 위와 같이 결과가 출력됩니다. 다음 SQL문은 아래와 같습니다.

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ MEMBERTBL 테이블에서는 MEMBERID, MEMBERNAME, MEMBERADDRESS 속성 값에 Son, Lee, Hwang 과 같은 튜플 값들이 나열되어 있는 것을 확인할 수 있습니다.

INSERT INTO MEMBERTBL VALUES ('Cho', '규성', '전북현대');

1 행 이(가) 삽입되었습니다.

▶ 다음은 MEMBERTBL 테이블에 조규성 선수를 INSERT 문을 통해서 추가해 보도록 하겠습니다. VALUES 값에 'Cho' = MEMBERID, '규성'=MEMBERNAME, '전북현대'=MEMBERADDRESS의 데이터를 차례로 입력합니다.

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Cho	 규성     전북현대
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ MEMBERTBL 테이블을 SELECT문을 통해 조회했을때 데이터가 삽입되어 출력되는 것을 확인할 수 있습니다. 다만, 조규성이라는 MEMBERNAME과 대한민국 전북현대라는 MEMBERADDRESS로 입력을 해야 정확한데 잘못된 데이터 입력을 통해 삭제를 해보도록 하겠습니다.

DELETE FROM MEMBERTBL WHERE MEMBERID = 'Cho';

1 행 이(가) 삭제되었습니다.

▶ DELETE FROM MEMBERTBL WHERE MEMBERID = 'Cho' SQL문을 통해서 해당 행을 삭제하도록 합니다.

INSERT INTO MEMBERTBL VALUES ('Cho', '조규성', '대한민국 전북현대');

1 행 이(가) 삽입되었습니다.

▶ 정확한 데이터 값에 맞도록 INSERT문을 통해서 데이터를 입력해주도록 합니다.

▶ MEMBERTBL 테이블을 SELECT문을 통해 조회했을때 데이터가 삽입되어 출력되는 것을 확인할 수 있습니다. 이번에는 정확하게 MEMBERNAME=조규성, MEMBERADDRESS=대한민국 전북현대 데이터 값이 조회가 된 것을 확인할 수 있습니다.

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Cho      조규성   대한민국 전북현대   
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ SELECT * FROM MEMBERTBL; SQL문을 통해서도 동일하게 확인할 수 있습니다. 

UPDATE MEMBERTBL SET MEMBERADDRESS = '스코틀랜드 셀틱' WHERE MEMBERID = 'Cho';

1 행 이(가) 업데이트되었습니다.

▶ 이번에는 조규성 선수의 데이터 조회시 이적으로 인해 소속팀이 변경됐을 경우 해당 데이터를 변경하기 위해서 UPDATE문을 통해 해당 선수의 데이터 값을 변경하도록 하겠습니다.
UPDATE MEMBERTBL SET MEMBERADDRESS = ' 스코틀랜드 셀틱' WHERE MEMBERID = 'Cho'

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Cho      조규성   스코틀랜드 셀틱     
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ MEMBERTBL 테이블의 결과 값을 조회했을 경우 조규성 선수의 MEMBERADDRESS 데이터 값이 대한민국 전북현대에서 스코틀랜드 셀틱으로 변경된 것을 확인할 수 있습니다.

▶ 다음은 현재 MEMBERTBL 테이블에서 존재하는 현역선수가 있다고 가정한다면 은퇴를 하게 되면 해당 테이블에서 선수가 삭제되어야 합니다.
그런 경우 은퇴한 선수 리스트 목록을 보관하기 위해 은퇴선수 리스트 테이블을 생성하고 트리거를 통해 자동으로 관리가 될 수 있도록 해보도록 하겠습니다.

INSERT INTO MEMBERTBL VALUES ('Park', '박지성', '잉글랜드 맨체스터유나이티드');
INSERT INTO MEMBERTBL VALUES ('Song', '송종국', '네덜란드 페예노르트');

1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.

▶ 우선 박지성, 송종국 선수를 MEMBERTBL 테이블에 INSERT문을 통해서 추가해주도록 합니다.

▶ MEMBERTBL 테이블을 SELECT 했을때 MEMBERID=Park, Song  MEMBERNAME = 박지성, 송종국 MEMBERADDRESS = 잉글랜드 맨체스터유나이티드, 네덜란드 페예노르트 데이터 값이 추가되어 해당 테이블에 서 조회가 되는 것을 확인할 수 있습니다. 

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Cho      조규성   스코틀랜드 셀틱     
Park     박지성   잉글랜드 맨체스터유나이티드
Song     송종국   네덜란드 페예노르트 
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리     

7개 행이 선택되었습니다.

▶ MEMBERTBL 테이블을 SELECT SQL문을 통해서 7개의 DATA 행이 출력되는 것을 확인할 수 있습니다.

▶ 이제 트리거를 통한 자동관리 테이블을 관리하기 전에 은퇴한 선수들을 보관할 수 있는 테이블 하나를 생성하도록 합니다.

CREATE TABLE DELETEDMEMBERTBL 
(
    MEMBERID CHAR(8), MEMBERNAME NCHAR(5), MEMBERADDRESS NVARCHAR2(20),
    DELETEDDATE DATE
);

Table DELETEDMEMBERTBL이(가) 생성되었습니다.

▶ DELETEDMEMBERTBL 테이블 하나를 생성하고 속성 값을 MEMBERID CHAR(8), MEMBERNAME NCHAR(5), MEMBERADDRESS NVARCHAR2(20)로 컬럼을 생성하고 마지막에 삭제된 날짜를 표기할 수 있는 DATE 속성 값까지 추가하여 테이블을 생성하도록 합니다.

SELECT * FROM DELETEDMEMBERTBL;

선택된 행 없음

▶ 생성된 DELETEDMEMBERTBL을 SELECT 조회시 데이터가 없는 것을 확인할 수 있습니다.

▶ MEMBER 테이블에 있는 데이터들이 트리거를 통해서 자동으로 관리가 되고 삭제가 된 데이터에 대해서 DELETEDMEMBERTBL에 생성될 수 있도록 트리거를 생성하도록 합니다.

CREATE TRIGGER TRG_DELETEMEMBERTBL --트리거 이름
    AFTER DELETE -- 삭제 후에 작동하게 지정
    ON MEMBERTBL -- 트리거를 부착할 테이블
    FOR EACH ROW -- 각 행마다 적용됨
BEGIN
    -- : OLD 테이블의 내용을 백업테이블에 삽입
    INSERT INTO DELETEDMEMBERTBL
        VALUES (:old.MEMBERID, :old.MEMBERNAME, :old.MEMBERADDRESS, SYSDATE() );
END;

Trigger TRG_DELETEMEMBERTBL이(가) 컴파일되었습니다.

▶TRG_DELETEMEMBERTBL라는 트리거를 생성하고 MEMBERTBL 테이블에서 삭제되는 데이터들이 DELETEDMEMBERTBL 테이블 안에   old.MEMBERID, :old.MEMBERNAME, :old.MEMBERADDRESS, SYSDATE() 속성 값으로 자동 저장되면서 은퇴 선수의 목록 리스트를 별도로 보관할 수 있게 됩니다.

★Trigger 생성 설명★
1) CREATE TRIGGER TRG_DELETEMEMBERTBL = 트리거 이름
2) AFTER DELETE = 삭제 후에 작동하게 지정
3) ON MEMBERTBL = 트리거를 부착할 테이블
4) FOR EACH ROW = 각 행마다 적용됨
5) BEGIN  INSERT INTO DELETEDMEMBERTBL VALUES
(:old.MEMBERID, :old.MEMBERNAME, :old.MEMBERADDRESS, SYSDATE() ); = : OLD 테이블의 내용을 백업테이블에 삽입

DELETE FROM MEMBERTBL WHERE MEMBERNAME = '박지성';

1 행 이(가) 삭제되었습니다.

▶ MEMBERTBL에 존재하는 박지성 선수를 삭제해보록 하겠습니다.

SELECT * FROM MEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS       
-------- ----- --------------------
Cho      조규성   스코틀랜드 셀틱     
Song     송종국   네덜란드 페예노르트 
Son      손흥민   잉글랜드 토트넘     
Lee      이강인   스페인 마요르카     
Hwang    황희찬   잉글랜드 울버햄튼   
Kim      김민재   이탈리아 나폴리

▶ 삭제 후 MEMBERTBL 테이블을 조회 했을때 박지성 선수의 데이터 값은 존재하지 않는 것을 확인할 수 있습니다.

▶DELETEDMEMBERTBL 테이블에서 조회시 박지성 선수가 Trigger에서 지정한 속성 값에 따라 데이터가 입력되어 저장된 것을 확인할 수 있습니다.

SELECT * FROM DELETEDMEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS        DELETEDD
-------- ----- -------------------- --------
Park     박지성   잉글랜드 맨체스터유나이티드 23/01/09

▶ SQL문을 통해서도 DELETEDMEMBERTBL 테이블 조회시 확인할 수 있습니다.

DELETE FROM MEMBERTBL WHERE MEMBERNAME = '송종국';

1 행 이(가) 삭제되었습니다.

▶ 마찬가지로 이번에는 MEMBERTBL에 존재하는 송종국 선수를 삭제해 보도록 하겠습니다.

▶MEMBERTBL에 존재하는 송종국 선수까지 삭제 후 DELETEDMEMBERTBL 테이블에서 2명의 선수가 은퇴선수 리스트로 저장되어 보존된 것을 확인할 수 있고  SYSDATE를 통해서 은퇴 날짜 시점인 23/01/09일 또한 확인할 수 있습니다.

SELECT * FROM DELETEDMEMBERTBL;

MEMBERID MEMBE MEMBERADDRESS        DELETEDD
-------- ----- -------------------- --------
Park     박지성   잉글랜드 맨체스터유나이티드 23/01/09
Song     송종국   네덜란드 페예노르트  23/01/09

▶ DELETE 된 선수 목록이 DELETEDMEMBERTBL 테이블에서 조회를 할 수 있습니다. 앞선 DELETE SQL문을 통해서 삭제된 박지성, 송종국 선수의 데이터가 트리거를 통해서 자동으로 DELETEDMEMBERTBL에 등록이 된 것이라는 것 또한 확인할 수 있습니다.

DROP  TRIGGER TRG_DELETEMEMBERTBL;

Trigger TRG_DELETEMEMBERTBL이(가) 삭제되었습니다.

▶ 자동관리를 통해 은퇴선수의 테이블을 더 이상 저장하지 않기 위해서 생성된 트리거를 삭제하도록 합니다.

DROP TABLE DELETEDMEMBERTBL;

테이블이 삭제되었습니다.

▶ 테스트를 위해 트리거도 삭제를 하였고 자동관리 시스템을 사용하지 않기 때문에 은퇴 후 저장되는 테이블 DELETEDNENBERTBL도 DROP SQL문을 통해서 삭제하도록 합니다.

 

2023.01.11 - [Programming and Database/SQL] - DB Oracle 뷰(View) 테이블 생성 및 저장 프로시저 사용하는 방법

DB Oracle 뷰(View) 테이블 생성 및 저장 프로시저 사용하는 방법

2022.12.22 - [Programming and Database/SQL] - DB Oracle 스키마 생성하는 방법

DB Oracle 스키마 생성하는 방법

 

PS. 포스팅의 내용은 제 기준으로 작성한 것이기 때문에 참고해서 보시면 감사하겠습니다.

포스팅 과정의 오류 및 오타 그리고 궁금한 점에 대해서는 댓글 남겨주세요.