DBeaver)Trigger 사용하기

DBMS 보면 Trigger 메뉴가 있습니다.

어디다 쓰는 것이냐면 데이터베이스에 INSERT UPDATE DELETE 하기 전에

자동으로 실행하고 싶은 코드가 있으면 Trigger 메뉴 들어가서 설정해놓으면 됩니다. 

그럼 정말 자동으로 실행되는데 

 

- 데이터를 다른 테이블에 반영하고 싶을 때

- 데이터 넣기 전에 데이터를 깔끔하게 정제하고 싶을 때 

- 테이블 변경기록 (로그)를 다른 테이블에 저장해두고 싶을 때

- 테이블의 통계를 다른 테이블에 저장해두고 싶을 때 

대충 이런 경우에 사용하면 편리합니다. 

 

 

 

 

 

테이블 2개 준비해봅시다 

 

예를 들어 product 테이블과 counter 테이블이 있다고 칩시다.

 

 

▲ product 테이블은 그냥 상품저장용임 

상품도 하나 저장해봅시다. 

 

 

▲ 이건 counter 테이블인데 product 안에 있는 상품갯수 기록용으로 하나 만들었습니다. 

 

 

CREATE TABLE trigger_test.product(
  상품명 varchar(100),
  가격 int
);
CREATE TABLE trigger_test.counter(
  자료 varchar(100),
  값 int
) 

직접 만들기 귀찮으면 DDL 문법 실행합시다. 

 

 

근데 갑자기 product 테이블에 데이터 넣을 때 마다

counter 테이블에 있던 상품갯수 숫자에 +1을 해주고 싶은겁니다. 

그럼 데이터 넣을 때 마다 UPDATE counter 어쩌구 코드를 짜도 되겠지만

자동화하고 싶으면 trigger 로 만들어놓으면 됩니다.

 

 

 

 

 

 

Trigger 사용법

 

Trigger는 INSERT/UPDATE/DELETE 실행시 자동으로 함께 실행되는 코드를 뜻합니다. 

Trigger를 하나 만들고 싶으면 아래의 템플릿 복붙하고 필요한거 바꾸면 됩니다.

이런건 외우는게 아니라 복붙해서 쓰는 것임 

 

 

DROP TRIGGER IF EXISTS db명.트리거이름;
DELIMITER $$
CREATE TRIGGER db명.트리거이름
AFTER INSERT ON db명.테이블명 
FOR EACH ROW 
BEGIN

  실행할코드~~

END $$
DELIMITER ; 

1. 트리거 이름을 작명합시다. 

2. 트리거가 동작할 조건을 결정해줍니다.

AFTER/BEFORE 중에 하나 고르고 INSERT/UPDATE/DELETE 중에 하나 고르면 됩니다. 

 

3. FOR EACH ROW도 잘 적어두면 모든 행에 적용됩니다.

4. BEGIN ~ END 사이에 실행할 코드 적으면 됩니다. 프로그래밍가능

 

 

 

 

 

 

그래서 product 테이블에 INSERT시 자동으로 뭔가 실행하려면

 

DROP TRIGGER IF EXISTS trigger_test.엄청난트리거;
DELIMITER $$
CREATE TRIGGER trigger_test.엄청난트리거
AFTER INSERT ON trigger_test.product 
FOR EACH ROW 
BEGIN

  UPDATE trigger_test.counter SET 값 = 값 + 1 WHERE 자료 = '상품갯수' ;

END $$
DELIMITER ; 

그래서 아까 만들고 싶었던 기능을 위해 이렇게 작성해봤습니다.

이러면 product 테이블에 뭔가 INSERT 후에 자동으로 UPDATE문도 실행해줍니다.

그리고 UPDATE문에는 counter 테이블의 값 부분을 +1 하라고 코드짜놨습니다.

 

이제 product 테이블에 뭔가 INSERT 해보십시오. counter 테이블에도 +1됨 ㄷㄷ 

 

 

 

 

Q. counter 테이블은 통계내는 용도같은데 그냥 product 테이블에 COUNT() 쓰면 되는거 아님?

- 맞는데 그런 통계내는 함수들을 너무 자주 쓰면 성능에 안좋을까봐

따로 테이블만들어서 통계를 기록해두는 곳들도 있습니다. 

아니면 id 컬럼이 있으면 가장 큰 id만 출력하라고 해도 COUNT()랑 비슷할듯요 

 

 

 

 

 

 

OLD, NEW 문법

 

 

트리거 만들 때 사용할 수 있는 OLD, NEW 라는 변수같은게 있습니다. 

OLD 안에는 변경전 자료, NEW 자료는 변경후 자료를 담고 있습니다. 

OLD는 그래서 UPDATE 전, DELETE 전의 자료가 담겨있고

NEW는 그래서 UPDATE 후, INSERT 후의 자료가 담겨있습니다.

그래서 이거 쓰면 새로 넣을 자료를 검사하거나 그럴 수도 있습니다.

 

 

DROP TRIGGER IF EXISTS trigger_test.엄청난트리거;
DELIMITER $$
CREATE TRIGGER trigger_test.엄청난트리거
BEFORE INSERT ON trigger_test.product 
FOR EACH ROW 
BEGIN
  IF NEW.가격 IS NULL 
    THEN SET NEW.가격 = 1000;
  END IF;
END $$
DELIMITER ; 

▲ DBeaver 들어가서 기존에 있던 Trigger를 이렇게 수정해봅시다. 

(데이터베이스 눌러보면 메뉴들 중에 Trigger 들어가보면 수정가능합니다)

 

1. BEFORE INSERT로 바꾸고 

2. 만약에 새로 저장할 데이터 중 가격 부분이 NULL 이면 

3. 가격을 1000으로 강제로 설정하라고 코드짜봤습니다.

 

그럼 이제 가격을 까먹고 NULL로 저장했을 경우 1000이 강제로 들어갑니다.

이래놓으면 데이터 빵꾸를 조금이라도 미리 방지할 수 있습니다. 

 

 

 

 

 

 

 

단점 : 이 짓거리는 서버도 할 수 있음 

 

DB에 데이터를 INSERT/UPDATE/DELETE 하는건 서버가 주로 담당한다고 했습니다.  

그래서 데이터 깔끔하게 다듬기, 다른 테이블에 저장하기 이런건 서버에 코드짜놔도 됩니다.  

서버에 코드짜는게 Trigger 쓰는 것 보다 훨씬 에러체크하기 쉽다는게 장점이기도 합니다. 

 

 

 

 

오늘의 숙제 : 

 

Q1. product 테이블에 있던 데이터 삭제시 counter 테이블에 있던 숫자도 -1 되도록 trigger를 만들어봅시다. 

 

전 어떻게 했냐면

 

DROP TRIGGER IF EXISTS trigger_test.엄청난트리거;
DELIMITER $$ 
CREATE TRIGGER trigger_test.엄청난트리거 
AFTER DELETE ON trigger_test.product 
FOR EACH ROW 
BEGIN

    UPDATE trigger_test.counter SET 값 = 값 - 1 WHERE 자료 = '상품갯수';

END $$
DELIMITER ; 

따라치면 손만 아플 뿐입니다 

 

[collapse]

 

 

Q2. product 테이블에 있는 가격을 수정할 때 이전 가격 보다 10% 넘게 변동시

변경불가능하게 만드는 trigger를 만들어봅시다.  

 

알아서 고민해봅시다

 

DROP TRIGGER IF EXISTS trigger_test.엄청난트리거;
DELIMITER $$ 
CREATE TRIGGER trigger_test.엄청난트리거 
BEFORE UPDATE ON trigger_test.product 
FOR EACH ROW 
BEGIN

    IF (NEW.가격 - OLD.가격) / OLD.가격 > 0.1 THEN 
        SET NEW.가격 = OLD.가격; 
    END IF;

END $$
DELIMITER ; 

 

어떤 가격이 몇 퍼센트 올랐는지 구하고 싶으면 

(이전가격 - 오른가격) / 이전가격 x 100 해보면 됩니다. 

 

그래서 10% 이상 올랐다고 판단하면 그냥 NEW.가격을 OLD.가격으로 변경해버렸습니다.