[MySQL] Trigger

작성일 | In

Trigger (트리거)

  • 데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다

종류

  • 행 트리거 :
    • 테이블 안의 영향을 받은 행 각각에 대해 실행된다.
    • 변경 전 또는 변경 후의 행은 OLD, NEW라는 가상 줄 변수를 사용하여 읽을 수 있다
  • 문장 트리거 :
    • INSERT, UPDATE, DELETE 문에 대해 한번만 실행된다
    • 삽입, 갱신 또는 삭제되는 행 수에 관계없이 각 트랜잭션에 대해 명령문 레벨 트리거가 한 번 실행됩니다.

속성

  • BEFORE 또는 AFTER:
    • 트리거가 실행되는 시기를 지정한다.
  • INSTEAD OF
    • 트리거를 원래 문장 대신 수행한다.
  • WHEN
    • 트리거를 시작하는 조건식을 지정한다.

DB 별 트리거

  • 디비 마다 각각이 지원해주는 트리거가 있다.
  • 자세한 정보는 디비 트리거 정보 혹은 데이터베이스 트리거 참고하자.

MySQL 트리거

  • MySQL 은 5.0.2 부터 트리거를 지원한다.

  • MySQL은 행 수준 트리거 만 지원합니다.

  • BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, AFTER DELETE 트리거가 존재 한다.

트리거 생성 Example

1
2
3
4
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

1
2
3
4
CREATE TRIGGER trigger_name : 트리거를 생성 한다는 SQL문 trigger_name 에 트리거의 이름을 지정하자.
{BEFORE | AFTER} {INSERT | UPDATE| DELETE } : 언제, 어떤 작업에 트리거가 실행될지 설정한다.
ON table_name FOR EACH ROW : 어떤 테이블에 설정 할지 작성한다.
trigger_body: trigger 명령문을 작성한다. trigger 명령문이 여러줄이면 구분문자를 바꾸자.

트리거 수정자 가용성

1
2
3
4
트리거 이벤트	OLD	  NEW
INSERT	        X     O
UPDATE	        O     O
DELETE	        O     X

트리거 조회 및 삭제

조회

1
SHOW TRIGGERS;

삭제

1
DELETE TRIGGERS;

테스트 테이블을 만들자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(45) NOT NULL,
`phone` varchar(45) NOT NULL,
`alias` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `user_log` (
`user_id` int(11) NOT NULL,
`action` varchar(45) NOT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`alias` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_backup` (
`user_id` int(11) NOT NULL,
`email` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`alias` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • 간단하게 user, user_log, user_backup 테이블을 만들었다.
  • 이제 ALTER UPDATE, BEFORE DELETE, INSERT 의 프로시저를 만들어 보자.

AFTER 트리거 생성

  • AFTER 트리거는 INSERT, UPDATE, DELETE 명령문 후에 실행할수있는 트리거 입니다.
  • 회원이 탈퇴할때 회원 정보를 백업 시키는 AFTER DELETE 트리거를 만들어보겠습니다.
1
2
3
4
5
6
7
8
CREATE TRIGGER user_after_delete_trigger
    AFTER DELETE ON user
    FOR EACH ROW
INSERT INTO user_backup
SET user_id = OLD.id,
    email = OLD.email,
    phone = OLD.phone,
    alias = OLD.alias;

삭제 전 회원 데이터

삭제 전 회원 백업 데이터

1
2
DELETE FROM user
WHERE id = 7;

삭제 후 회원 데이터

삭제 후 회원 백업 데이터

BEFORE 트리거 생성

  • ALTER 트리거는 INSERT, UPDATE, DELETE 명령문 후에 실행할수있는 트리거 입니다.
  • 모든 행동에 트리거를 만들기 보다 BEFORE UPDATE 트리거를 생성해 보겠습니다.
  • 예를들어 유저정보를 가지고 있는 데이터베이스에서 유저 정보가 업데이트 되었을때 업데이트에 관한 로그를 남겨야할때 트리거를 이용하여 자동으로 실행되게 해보겠습니다.
1
2
3
4
5
6
7
8
9
CREATE TRIGGER user_log_trigger
    BEFORE UPDATE ON user
    FOR EACH ROW
INSERT INTO user_log
SET user_id = OLD.id,
    action = 'update',
    email = OLD.email,
    phone = OLD.lastname,
    alias = OLD.alias;
  • 트리거를 생성하고 기존의 회원 정보를 바꾼다고 생각하고 UPDATE 문을 실행해 보겠습니다.

업데이트 전 회원 데이터

1
2
3
4
5
UPDATE user
set email = "trigger",
    phone = "010-1111-1111",
    alias = "test"
where id = 1;

업데이트 쿼리 실행후 회원 데이터

업데이트 쿼리 실행후 로그 데이터

장점

  • 트리거는 데이터 무결성을 확인하는 다른 방법을 제공합니다.
  • 트리거는 데이터베이스 계층의 오류를 처리합니다
  • 트리거는 예약 된 작업 을 실행 하는 다른 방법을 제공 합니다.
    트리거를 사용 하면 테이블의 데이터를 변경 하기 전 이나 후에 트리거가 자동으로 호출되므로 예약 된 이벤트 가 실행될 때까지 기다릴 필요가 없습니다.
  • 트리거는 테이블의 데이터 변경 사항을 감사하는 데 유용 할 수 있습니다.

단점

  • 트리거는 모든 유효성 검사가 아닌 확장 된 유효성 검사 만 제공 할 수 있습니다.
    간단한 검증의 경우, 사용할 수있는 NOT NULL, UNIQUE, CHECK및 FOREIGN KEY제약.
  • 트리거는 데이터베이스에서 자동으로 실행되기 때문에 문제를 해결하기 어려울 수 있으며,
    클라이언트 응용 프로그램에서는 보이지 않을 수 있습니다.
  • 트리거는 MySQL 서버의 오버 헤드를 증가시킬 수 있습니다.

Reference

0%