[Udemy] Database Triggers

The Ultimate MySQL Bootcamp 18강을 공부하며 정리한 내용입니다.

Trigger란, 특정 테이블이 변경되었을때 자동으로 실행되는 sql 선언을 말한다.

데이터가 조건에 맞는 유효한 값인지 확인하거나, 쿼리문이 실행된 A 테이블의 변화를 B 테이블에 로깅하는 등의 경우에 쓸 수 있다.

syntax

CREATE TRIGGER trigger_name
  triger_time trigger_event
ON table_name 
FOR EACH ROW
  BEGING
  END;

• trigger_time
  BEFORE, AFTER로 작성 수 있다.

• trigger_event
  INSERT, UPDATE, DELETE를 작성할 수 있다.

• DELIMITER $$ (delimiter: 구분문자)
  기존에 ;로 sql문이 끝났음을 알려주었던 역할을 $$로 변경한다. trigger에서는 하나의 sql chunk에서 여러번 ;를 작성하기 때문에 끝났다고 인식하지 않도록 잠깐 설정을 바꿔는 것이다.

• $$
  위에서 설정한 내용을 토대로 sql문이 끝났음을 표시한다.

• DELIMITER ;
;로 sql 문이 끝났음을 표시하는 것으로 다시 설정을 바꿔준다.

• NEW
  NEW라는 가상변수를 사용해 NEW.칼럼명으로 테이블의 행에 영향(insert, update)을 받을 데이터를 가리킨다. (New is just a placeholder. It is referring to data that is attempted to get inserted)

• OLD
  OLD라는 가상변수를 사용해 OLD.칼럼명으로 테이블의 행에 영향(update, delete)을 받은 데이터를 가리킨다.

• SQLSTATE
  SQL 데이터베이스들에서 사용하는 일반적인 에러코드로 SIGNAL 키워드로 에러코드를 설정할 수 있다.

• MESSAGE_TEXT
  SET 키워드로 에러 발생시 보여줄 텍스트를 설정할 수 있다.

---

Example1 age over 18

데이터 유효성 조건에 맞는 데이터만 추가, 변경하고, 조건에 맞지 않는다면 에러를 발생시킨다.

DELIMITER $$

CREATE TRIGGER must_be_adult
     BEFORE INSERT ON users FOR EACH ROW
     BEGIN
          IF NEW.age < 18
          THEN
              SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Must be an adult!';
          END IF;
     END;
$$

DELIMITER ;

---

Example2 preventing self-follow

follower의 id와 followee id가 같은 경우, 트리거가 발생한다.

DELIMITER $$

CREATE TRIGGER prevent_self_follows
     BEFORE INSERT ON follows FOR EACH ROW
     BEGIN 
        IF NEW.follower_id = NEW.folowee_id
        THEN 
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'You cannot follow yourself!';
     END IF;
    END;
$$

DELIMITER ;

---

Example3 Logging Unfollows

유저가 다른 유저를 언팔로우할때(DELETE), unfollows 테이블에 데이터를 추가(INSERT)하는 트리거가 발생한다.

//follows와 이름만 다른 unfollows 테이블 생성
CREATE TABLE unfollows (
    follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

DELIMITER $$

CREATE TRIGGER capture_unfollow
     AFTER DELETE ON follows FOR EACH ROW
     BEGIN 
        //INSERT INTO unfollows (follow_id, followee_id) VALUES (OLD.follower_id, OLD.followee_id)
        INSERT INTO unfollows
        SET 
            follower_id = OLD.follower_id,
            followee_id = OLD.followee_id;
     END;
$$

DELIMITER ;

---

Managing Triggers

trigger는 한 번의 sql문으로 수백, 수천개의 데이터가 추가, 업데이트, 삭제될 수 있기 때문에 debugging을 어렵게 만들 수 있다는 점을 유의해야한다.

또한, trigger를 사용하지 않고도 구현할 수 있는 방법이 있다면, trigger를 지양하도록 한다. 예를 들어, 세 번째 예시에서는 새로운 테이블에 데이터를 삽입했는데 이 경우는 한번의 요청으로 많은 데이터가 생성될 수 있다. 따라서, 기존에 있는 follows 테이블에 status 칼럼을 추가해서 active, inactive로 변경하는 방식이 효율적일 수 있다.

SHOW triggers; //triggers 목록 확인하기
DROP TRIGGER trigger_name; //트리거 삭제하기