13. OBJECT(TRIGGER)

 

ㅁ PL/SQL에 이름을 붙여서 객체로 만들 수 있음. 프로시저로도 만들 수 있고 트리거로도 만들 수 있다.

- PL/SQL 코드를 객체화하여 저장 프로시저나 트리거로 만들 수 있습니다.

 

 

ㅁ < TRIGGER 트리거 >

- 특정 테이블에 변경사항(이벤트) 발생시(DML문) 묵시적으로(자동으로) 실행시킬 내용을 정의해둘 수 있는 데이터베이스 객체.

- 한번 정의해두면 내가 매번 실행 안해도 됨.

- PL/SQL 구문을 이용해서 생성

 

 

ㅁ 트리거 종류

 

(1) SQL문 실행시기에 따라

- BEFORE TRIGGER : 특정 테이블에 이벤트가 발생되기 전에 트리거 실행

- AFTER TRIGGER : 특정 테이블에 이벤트가 발생된 후에 트리거 실행

 

 

(2) SQL문에 영향을 받는 행수에 따라

 

- STATEMENT TRIGGER(문장 트리거) : 이벤트가 발생한 SQL문에 대해 딱 한번만 트리거 실행

이벤트가 발생한 행이 여러 행이어도 트리거는 딱 한번만 실행된다.

 

- ROW TRIGGER(행 트리거) : 이벤트가 발생한 행 수만큼 매번 트리거 실행.

행 트리거는FOR EACH ROW 옵션 기술.

 

 

 

키워드 :OLD - BEFORE UPDATE(수정 전 데이터), BEFORE DELETE(삭제 전 데이터)

 

키워드 :NEW - AFTER INSERT(추가된 데이터), AFTER UPDATE(수정 후 데이터)

 

 

 

UPDATE면 수정 전, 후 데이터(:OLD, :NEW)를 쓸 수 있다.

INSERT면 :NEW, DELETE면 :OLD를 쓸 수 있다.

 

그 행에서 특정 컬럼만 뽑아서 쓸 수 있다는 뜻이다.

FOR EACH ROW 옵션시에만 쓸 수 있다.

:NEW가 INSERT된 한 행에 대한 정보를 가지고 있다.

 

 

 

 

 

ㅁ 트리거가 사용될만한 예시

- 회원탈퇴시 회원테이블에 DELETE 이벤트 발생하면

탈퇴회원테이블에 자동으로 INSERT 처리.

 

- 회원신고시 신고테이블에 INSERT 이벤트 발생하면

일정 수를 넘겼을 경우 

회원테이블에 회원상태를 자동으로 블랙리스트로 UPDATE 처리.

 

- 상품입출고시 입출고테이블에 INSERT 이벤트 발생하면 

상품테이블에 해당 상품의 재고수량을 자동으로 UPDATE 처리.

 

 

 

 

 

 

ㅁ 트리거 생성

 

CREATE [OR REPLACE] TRIGGER 트리거명

BEFORE | AFTER     INSERT | UPDATE | DELETE  ON  테이블명

[FOR EACH ROW]   

 

DELCARE

        변수선언;

BEGIN

        실행내용(위에 지정된 테이블에 이벤트 발생시 묵시적(자동)으로 실행할 구문)

EXCEPTION

        예외처리구문;

END;

/

 

- 이벤트를 감지할 테이블을 지정한다.

- FOR EACH ROW를 삽입하면 행트리거로 지정한다.

 

- 이벤트가 일어났을 때 실행할 내용을 하단에 PL/SQL 구문으로 작성한다.

- DECLARE 부분과 EXCEPTION 부분은 생략 가능. BEGIN은 무조건 작성해야 한다.

 

 

 

ㅇ EMPLOYEE 테이블에 새로운 행이 INSERT될 때 자동으로 메시지 출력되는 트리거 정의

 

SET SERVEROUTPUT ON;

 

CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT ON EMPLOYEE

BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원님 환영합니다.');
END;
/

 

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, DEPT_CODE, JOB_CODE, HIRE_DATE)
VALUES (500, '이순신', '123456-1234567', 'D7', 'J7', SYSDATE);

 

 

- 이 트리거는 EMPLOYEE 테이블에 INSERT될 때마다 실행된다.

 

 

- 자동으로 실행할 내용을 지금은 출력문으로 작성했지만 또 다른 SQL문으로 작성할 수도 있다.

 

 

 

 

ㅇ (행트리거) EMP_DEPT 테이블에 UPDATE 이벤트 발생시 자동으로 메시지 출력

 

 

- 이 업데이트에 의해서 변경된 행은 4개인데 트리거는 한번만 작동함. (문장 트리거)

 

 

 

CREATE OR REPLACE TRIGGER TRG_02
AFTER UPDATE ON EMP_DEPT
FOR EACH ROW

BEGIN
   DBMS_OUTPUT.PUT_LINE('정보가 수정되었습니다.'); 
END;
/

 

[실행 결과]

정보가 수정되었습니다.
정보가 수정되었습니다.
정보가 수정되었습니다.
정보가 수정되었습니다.

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

 

 

- UPDATE면 수정 후 데이터를 쓸 수 있다.

키워드 :NEW를 쓸 수 있다.

특정 컬럼만 뽑아서 쓸 수 있다는 뜻이다.

FOR EACH ROW 옵션시에만 쓸 수 있다.

:NEW가 INSERT된 한 행에 대한 정보를 가지고 있다.

 

 

 

 

ㅁ 예제 (상품 입고 및 출고 관련 예시)

 

 

(1) 상품에 대한 데이터를 보관할 테이블 생성 (TB_PRODUCT)

 

CREATE TABLE TB_PRODUCT (
    PCODE VARCHAR2(8) PRIMARY KEY
  , PNAME VARCHAR2(30) NOT NULL
  , BRAND VARCHAR2(30) NOT NULL
  , PRICE NUMBER NULL
  , STOCK NUMBER DEFAULT 0 NOT NULL
);

 

- 아무것도 안쓰면 자동으로 NULL 제약조건을 준 것과 같다. 그냥 통일감 있게 쓰기도 함.

 

 

(2) 상품코드로 활용할 시퀀스 생성 (SEQ_PCODE)

 

CREATE SEQUENCE SEQ_PCODE
    NOCACHE;

 

 

(3) 샘플데이터 추가

 

 

 

- STOCK은 기본값 설정되어 있어서 NOT NULL임에도 불구하고 대입하지 않아도 괜찮다.

 

 

 

(4) 상품 입출고 이력 테이블 생성 (TB_PRODETIAL)

    
CREATE TABLE TB_PRODETAIL (
    DCODE NUMBER        PRIMARY KEY
  , PCODE VARCHAR2(8)   REFERENCES TB_PRODUCT
  , PDATE DATE          NOT NULL
  , AMOUNT NUMBER       NOT NULL
  , STATUS CHAR(6)      CHECK(STATUS IN ('입고', '출고'))
);

 

(5) 이력번호로 활용할 시퀀스 생성 (SEQ_DCODE)

 

CREATE SEQUENCE SEQ_DCODE
    START WITH 10000
    NOCACHE;

 

 

 

※ 입고 또는 출고 기능

- 입출고이력 테이블에 데이터 INSERT

- 상품 테이블 재고수량을 그만큼 UPDATE

 

 

(6) PRO_001 상품이 오늘날짜로 10개 입고

 

 

INSERT INTO TB_PRODETAIL
       VALUES (SEQ_DCODE.NEXTVAL, 'PRO_001', SYSDATE, 10, '입고');
    
UPDATE TB_PRODUCT 
SET STOCK = STOCK + 10
WHERE PCODE = 'PRO_001'
;
    

COMMIT; 

 

- 기능단위로 트랜잭션으로 묶어서 처리해야 한다.

- 직접 수작업으로 연동처리를 하고 있다.

 

 

 

(7) PRO_002 상품이 오늘날짜로 5개 출고

 

INSERT INTO TB_PRODETAIL
       VALUES (SEQ_DCODE.NEXTVAL, 'PRO_002', SYSDATE, 5, '출고');  
    
UPDATE TB_PRODUCT 
SET STOCK = STOCK - 5
WHERE PCODE = 'PRO_002'
;
    
COMMIT;    

 

 

 

(8) PRO_003 상품이 오늘날짜로 20개 입고

 

 

INSERT INTO TB_PRODETAIL
       VALUES (SEQ_DCODE.NEXTVAL, 'PRO_003', SYSDATE, 20, '입고');  
    
UPDATE TB_PRODUCT 
SET STOCK = STOCK - 20         //      실수
WHERE PCODE = 'PRO_003'
;
    
ROLLBACK;    

 

- 실수로 +20이 아닌 -20을 해버림.

이 기능은 실패했다. INSERT는 잘 됐는데 UPDATE는 안됐음.

이러면 둘 다 롤백되어야 함.

- 실수를 방지하기 위해 자동으로 업데이트 되게끔 트리거를 사용할 수 있다.

 

 

(9) 트리거 정의

- TB_PRODETAIL 테이블에 INSERT 이벤트 발생 후

자동으로 TB_PRODUCT 테이블의 STOCK 값을 UPDATE 해주는 트리거 정의.

 

 

- INSERT된 데이터의 STATUS값이 '입고'일 경우

 

UPDATE TB_PRODUCT

SET STOCK = STOCK  +  INSERT된데이터의AMOUNT

WHERE PCODE = INSERT된데이터의PCODE;

 

- INSERT된 데이터의 STATUS값이 '출고'일 경우

 

UPDATE TB_PRODUCT

SET STOCK = STOCK  -  INSERT된데이터의AMOUNT

WHERE PCODE = INSERT된데이터의PCODE;

 

 

 

 

CREATE OR REPLACE TRIGGER TRG_001
AFTER INSERT ON TB_PRODETAIL
FOR EACH ROW

BEGIN
    IF :NEW.STATUS = '입고'
        THEN 
            UPDATE TB_PRODUCT
            SET STOCK = STOCK  +  :NEW.AMOUNT
            WHERE PCODE = :NEW.PCODE;
    ELSE 
            UPDATE TB_PRODUCT
            SET STOCK = STOCK  -  :NEW.AMOUNT
            WHERE PCODE = :NEW.PCODE;   
    END IF;         
END;
/

 

- 이제 TB_PRODETAIL에 INSERT하면 자동으로 TB_PRODUCT의 STOCK값이 UPDATE됨. 

 

 

 

ㅇ PRO_003 상품이 오늘날짜로 7개 출고

 

INSERT INTO TB_PRODETAIL

VALUES (SEQ_DCODE.NEXTVAL, 'PRO_003', SYSDATE, 7, '출고');

 

- TB_PRODUCT도 업데이트 됨.

 

ㅇ PRO_004 상품이 오늘날짜로 100개 입고

 

INSERT INTO TB_PRODETAIL

VALUES (SEQ_DCODE.NEXTVAL, 'PRO_003', SYSDATE, 100, '입고');