[PL/SQL] 트리거 trigger

[트리거]

테이블에 부착한 형태로써, insert, update, delete 작업이 수행될 때 특정 코드가 작동되도록 하는 구문

= 연쇄적인 동작 

트리거 생성 > 부착   > 동작 > DML 수행

set SERVEROUTPUT on; -- 출력구문을 사용가능하도록 온

CREATE OR REPLACE TRIGGER 트리거명
    AFTER DELETE OR UPDATE --삭제, 수정 이후에 동작
    ON 부착할테이블 
    FOR EACH ROW -- 각행에 적용
BEGIN
    DBMS_OUTPUT.PUT_LINE('트리거가 동작함'); -- 실행되는 코드를 begin~end에 넣음
END;

 

[ 자동 연관  ]

(주문 <-> 상품)
주문이 들어오면 상품이 줄어들어야하는데
이때 트리거를 통해서 관리하면 편하게 관리 가능
주문시 (+트리거)

 

 

[트리거에서 사용할 수 있는 키워드]

 

- :OLD 

= 참조 전 열의 값 (INSERT : 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)
- :NEW

= 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정 된 자료) -  :: 인서트 되기 전에 값을 작업 후 바꿈 

 

[AFTER트리거의 동작 형태]
- 트리거 생성 → 테이블 부착 → 테이블DML수행 → 트리거 동작

[시나리오]

테이블에 UPDATE OR DELETE가 수행되면 별도의 백업 테이블을 만들어서 이전내용을 기록한다.

-- 원본테이블과 백업테이블 생성
CREATE TABLE TBL_USER(
    ID VARCHAR2(20) PRIMARY KEY,
    NAME VARCHAR2(20),
    ADDRESS VARCHAR2(30)
);

CREATE TABLE TBL_USER_BACKUP(
    ID VARCHAR2(20),
    NAME VARCHAR2(20),
    ADDRESS VARCHAR2(30),
    UPDATEDATE DATE DEFAULT SYSDATE,
    M_TYPE CHAR(10), --변경타입
    M_USER VARCHAR2(20) --변경한 사용자
);

 

트리거생성

-- 트리거생성
CREATE OR REPLACE TRIGGER TRG_USER_BACKUP
    AFTER UPDATE OR DELETE --AFTER트리거
    ON TBL_USER --부착테이블
    FOR EACH ROW
DECLARE --사용할 변수를 선언하는 곳
    VN_TYPE VARCHAR2(10);
BEGIN
    IF UPDATING THEN -- UPDATING은 시스템에서 지원하는 구문중 하나
        VN_TYPE := '수정'; --위 변수에 수정을 저장
    ELSIF DELETING THEN -- DELETING되었다면
        VN_TYPE := '삭제';
    END IF;
    --실행구문시작 (:OLD 는 테이블 DELETE, UPDATE가 적용되기전 기존 데이터. 즉 변경전 데이터)
    INSERT INTO TBL_USER_BACKUP 
    VALUES(:OLD.ID, :OLD.NAME, :OLD.ADDRESS, SYSDATE, VN_TYPE, USER() );
END;

 

결과수행

INSERT INTO TBL_USER VALUES('TEST01', 'ADMIN', '서울');
INSERT INTO TBL_USER VALUES('TEST02', 'ADMIN', '경기');
INSERT INTO TBL_USER VALUES('TEST03', 'ADMIN', '부산');

UPDATE TBL_USER SET ADDRESS = '부산' WHERE ID = 'TEST01'; --트리거동작
DELETE FROM TBL_USER WHERE ID = 'TEST02'; --트리거동작

 

[BEFORE트리거의 동작 형태]
- 트리거 생성 → 테이블 부착 → 트리거 동작 → 테이블DML수행

 

[시나리오]

회원 정보가 입력될 때 회원 이름을 마스킹 해서 넣는다

-- 트리거 생성
CREATE OR REPLACE TRIGGER TRG_USER_INSERT 
    BEFORE INSERT -- 이전 트리거 
    ON TBL_USER
    FOR EACH ROW
BEGIN
    :NEW.NAME := SUBSTR(:NEW.NAME, 1, 1) || '**'; -- 마스킹 처리
END;

 

 

결과수행

INSERT INTO TBL_USER VALUES('TEST02', '이순신', '부산');
INSERT INTO TBL_USER VALUES('TEST03', '홍길동', '경기');
INSERT INTO TBL_USER VALUES('TEST04', '홍길동', '경기');