MySQL - 트리거

트리거 란?

트리거(Trigger)는 사전적 의미로 '방아쇠'라는 뜻입니다. MySQL에서 트리거는 테이블에서 어떤 이벤트가 발생했을 때 자동으로 실행됩니다. 즉, 어떤 테이블에서 특정한 이벤트가 발생했을 때, 실행시키고자 하는 작업들을 자동으로 수행할 수 있게끔 트리거를 미리 설정해 두는 것입니다. 예를 들어 A 테이블의 내용이 삭제 되었을 시에 백업 테이블에 삭제된 내용, 삭제한 사용자, 시간을 기록하게 할 수 있습니다.

 

• 직접 실행시킬 수 없고 해당 테이블에 해당 이벤트가 발생할 때 실행된다.
• 트리거를 사용해서 데이터 무결성을 유지할 수 있다.
• MySQL에서는 View에 부착할 수 없다.

 

트리거 형식

DROP TRIGGER IF EXISTS trig1 -- 트리거가 이미 있을 시 삭제
DELIMITER //
CREATE TRIGGER trig1 -- 트리거 이름
    BEGIN|AFTER INSERT|UPDATE|DELETE [FOLLOWS|PRECEDES] -- 이벤트(트리거 발동 조건) 지정
    ON table1 -- 테이블 이름
    FOR EACH ROW
BEGIN
    ... 트리거 실행시 작동할 코드들
END //
DELIMITER ;

BEGIN 트리거는 이벤트가 발생하기 전 BEGIN의 코드부터 실행합니다. 예를들면, 입력될 데이터 값을 미리 확인해서 적절하지 않을 경우에 예외 처리를 하는데 사용할 수 있습니다.

AFTER 트리거는 이벤트가 발생한 후에 BEGIN의 코드를 실행합니다. 예를들면, 데이터 수정이나 삭제가 일어났을 때 변경된 내용이나 일자를 기록하는 데 사용할 수 있습니다.

 

동일한 조건을 갖는 다른 트리거도 있을 때 이를 다중트리거(Multiple Triggers)라 합니다. 이 때 트리거의 순위를 FOLLOWS로 설정하면 나중에 실행되고, PRECEDES로 설정하면 먼저 실행됩니다. 2개 이상의 트리거가 동일한 이벤트에서 작동한다면 트리거의 순위를 정해주는 것이 바람직합니다.

 

SHOW TRIGGERS FROM 데이터베이스명;

SHOW TRIGGERS문으로 데이터베이스에 생성된 트리거의 목록과 내용을 확인할 수 있습니다.

DROP TRIGGER 트리거명;

DROP TRIGGER문으로 특정 트리거를 삭제할 수 있습니다.

 

-- 1번 트리거
DROP TRIGGER IF EXISTS trig1
DELIMITER //
CREATE TRIGGER trig1
    AFTER INSERT
    ON table1
    FOR EACH ROW
BEGIN
    UPDATE table1 SET age = age+1
END //
DELIMITER ;

-- 2번 트리거
DROP TRIGGER IF EXISTS trig2
DELIMITER //
CREATE TRIGGER trig2
    AFTER UPDATE
    ON table1
    FOR EACH ROW
BEGIN
    ...
END //
DELIMITER ;

트리거의 BEGIN에서 실행되는 SQL문에서도 트리거가 작동됩니다. 위의 경우, table1에 대해 INSERT시에 trig1, UPDATE시에 trig2가 실행되고, trig1이 실행하면 UPDATE문이 실행되므로 trig2도 실행됩니다. 이를 중첩트리거(Nested Triggers)라고 합니다.

 

중첩 트리거가 몇개가 있던 가운데에서 한 문장이라도 실패할 시, 모든 내용은 롤백되므로 데이터 무결성을 유지할 수 있습니다. 

 

NEW 테이블, OLD 테이블

트리거에서 INSERT, UPDATE, DELETE 작업이 수행되면 임시로 시스템 테이블이 2개 생깁니다. NEW 테이블과 OLD 테이블입니다.

IF NEW.user_age > 150 THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '데이터의 입력이 잘못되었습니다.';
END IF;

예를 들어, INSERT 시에 트리거가 작동될 때 NEW 테이블의 열을 이용하면 테이블의 변경될 내용을 이용할 수 있습니다. 위의 코드를 BEGIN 안에 넣으면 나이가 150 초과로 입력되었을 때 잘못 입력된 것으로 간주하고 오류를 발생시키고 메시지를 출력합니다.