[ SQL ] 트리거 (TRIGGER)

 

트리거 (TRIGGER)

 

• 특정 테이블(TABLE)에 이벤트(INSERT, UPDATE, DELETE)가 발생하면 자동으로 실행되는 PROCEDURE(업무의 최소 단위, ALL OR NOTHING)
• 메모리를 차지하고 관리하기 쉽지 않음
• 그래서 독특한, 특별한 경우에 쓰는 게 좋다.

- 스키마 : 사용자(DATABASE 이름)

- 이름은 보통  TRG_, TRID_ 로 시작해서 어떤 업무를 위한 트리거인지 알아볼 수 있을 정도로 작성

- 기본 객체 : 어떤 테이블에 실행할 것인가

- 타이밍(BEFORE/AFTER)

- AFTER TRIGGER : INSERT 문의 데이터가 먼저 추가 되고 난 후에 트리거가 동작
- BEFORE TRIGGER : INSERT 문의 데이터가 추가되기 전에 먼저 트리거가 동작하고 난 후 데이터가 추가된다.

- 이벤트 : 어떤 이벤트가 발생할 때 실행 할 것인가, 여러 이벤트 발생 시 로도 설정가능하며 IF 문으로 각 이벤트 발생 시로 작성한다. 하지만 UPDATE 같은 경우는 타이밍 설정이 어떻게 되는지에 따라 결과가 많이 달라질 수 있기 때문에 조심하는 것이 좋다.

- 참조값은 이벤트 발생 시 이벤트 발생 전 테이터를 이전 항목(OLD), 이벤트 발생 후를 새 항목(NEW)이고 TEXTBOX 에 적어주는 값이 변수가 된다. 참조값을 쓸 때는 : 설정해준 변수 명, 참조할 필드명으로 써준다.

 

CREATE OR REPLACE TRIGGER TRG_TEST_TRIGGER
AFTER INSERT ON TEST_TBL 
REFERENCING OLD AS O NEW AS N 
FOR EACH ROW
BEGIN

	NULL;
    
END;

 

트리거는 그림 또는 쿼리로 트리거 생성이 가능하며 마지막으로 명령문 레벨 설정은 그림에서 명령문 체크박스 체크가 쿼리로는 아무것도 작성하지 않는 것이고 명령문 체크박스 체크 해제는 쿼리로 FOR EACH ROW 라 작성해준다. 체크박스 체크 시 트리거를 실행했을 때 데이터 처리를 테이블 전체 단위로 처리하겠다는 것이고 체크박스 해제 시  데이터 처리를 테이블의 ROW 단위로 하겠다는 것이다.

 

 

 

INSERT

테이블에 INSERT 이벤트가 발생하면 새로운  ROW가 만들어졌다는 것이다. 그렇기에 트리거에서는 참조값이 NEW만 존재하게 될 것이다. 

 

UPDATE

테이블에 UPDATE 이벤트가 발생하면 기존 데이터(ROWS) 중에 특정 필드값을 새로운 데이터(ROWS)로 수정했다는 것이다. 그렇기에 트리거에서는 참조값이 OLD와 NEW 모두 존재한다는 것을 의미한다.

 

DELETE

테이블에 DELETE 이벤트가 발생하면 기존 데이터(ROWS)가 삭제되었다는 것이다. 그렇기에 트리거에서는 참조값이 OLD 만 존재하게 된다.

 

 위 세 가지는 타이밍을 결정할 때 고려해야만 할 것이다. 가령 INSERT 이벤트 발생 시 트리거 타이밍을 BEFORE 로 주고 참조값을 사용하여 업무를 처리하고자 할 때, INSERT 이벤트 실행 전 트리거가 우선 실행되기 때문에 참조할 값이 없어 원하는 업무를 처리할 수 없을 것이다.

 

 

 

 

트리거(TRIGGER) 예제 

 

CREATE TABLE CUSTOMERS
(
    CID     CHAR(6)     PRIMARY KEY,
    CNAME   VARCHAR2(30)    NOT NULL,
    POINT   NUMBER(6)       NOT NULL
)
;

INSERT INTO CUSTOMERS VALUES ('C00001', '홍길동', 0);
INSERT INTO CUSTOMERS VALUES ('C00002', '전우치', 0);

SELECT * FROM CUSTOMERS;

-- COMMIT;

 

CREATE TABLE BUY
(
    BID     CHAR(4)         PRIMARY KEY,
    CID      CHAR(6)        NOT NULL,
    PNAME    VARCHAR2(40)   NOT NULL,
    PRICE   NUMBER(6)       NOT NULL,
    QTY     NUMBER(4)       NOT NULL
)
;

SELECT * FROM BUY;

-- COMMIT;

 

 

 

예제 1. 손님이 물건을 구매해서 BUY 테이블에 INSERT 이벤트가 발생할 경우 손님에게 구매금액의 2%의 포인트를 주는 트리거를 만들어 보자.

 

CREATE OR REPLACE TRIGGER TRG_INS_BUY_ADDPOINT 
AFTER INSERT ON BUY 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW

BEGIN

  -- BUY 에 INSERT 가 발생하면 CUSTOMERS 에 POINT UPDATE
  UPDATE CUSTOMERS
  SET POINT = POINT + (:NEW.PRICE * :NEW.QTY * 0.02)
  WHERE CID = :NEW.CID
  ;
  
END;

 

INSERT INTO BUY VALUES('B001', 'C00001', '아이스크림', 3000, 3);
INSERT INTO BUY VALUES('B002', 'C00002', '새우깡', 900, 3);

 

INSERT 발생 시 포인트가 UPDATE 된 것을 볼 수 있다.

타이밍 BEFORE 설정 시 INSERT 된 데이터가 없어 참조 값이 없으므로 원하는 업무 처리가 되지 않는다.

 

BUY 테이블 (우측) CUSTOMERS 테이블 (좌측)

 

예제 2. 손님이 구매한 물품을 반품하여 구매 테이블에 DELETE 이벤트가 발생할 경우 손님의 포인트를 반품된 만큼 차감하는 트리거를 만들어보자.

 

CREATE OR REPLACE TRIGGER TRG_DEL_BUY_DELPOINT 
BEFORE DELETE ON BUY
REFERENCING OLD AS O NEW AS N 
FOR EACH ROW 
BEGIN
  
  -- 반품되면 POINT 도 원상복구
  UPDATE CUSTOMERS
  SET POINT = POINT - (:O.PRICE * :O.QTY * 0.02)
  WHERE CID = :O.CID
  ;
  
END;

 

DELETE FROM BUY WHERE BID = 'B002';

DELETE 가 발생하면 포인트가 차감된 것을 볼 수 있다.

타이밍 ATFER 설정 시 DELETE 되어 버렸기 때문에 참조 값이 없으므로 원하는 업무 처리가 되지 않는다.

 

BUY 테이블 (우측) CUSTOMERS 테이블 (좌측)