[MySQL] 트리거 (종류, 사용법, 삭제, 예제)

종류

1) AFTER 트리거 

테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어났을 때 작동하는 트리거를 말한다.

2)BEFORE 트리거

테이블에 INSERT, UPDATE, DELETE 등의 작업이 일어나기 전에 작동하는 트리거를 말한다.

 

사용법

CREATE

    [DEFINER = user]

    TRIGGER trigger_name

    ON tbl_name FOR EACH ROW

    [trigger_order]

    trigger_body

trigger_time : { BEFORE | AFTER }

trigger_event : { INSERT | UPDATE | DELETE }

trigger_order : { FOLLOWS | PRECEDES } other_trigger_name

설명)

trigger_time에서 BEFORE와 AFTER를 지정할 수 있음

trigger_event에서 INSERT/UPDATE/DELETE 중에 하나를 지정할 수 있음

trigger_order는 테이블에 여러 개의 트리거가 부착되어 있을 때, 우선순위를 지정할 수 있음

 

삭제

DROP TRIGGER TRIGGER명;

반응형

예제

1. AFTER 트리거

회원 테이블에 UPDATE 또는 DELETE를 시도하면 수정 또는 삭제된 데이터를 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록

1) 저장할 테이블을 1개 생성

USE sqlDB;

CREATE TABLE backup_userTbl;

( userID CHAR(8) NOT NULL PRIMARY KEY,

  name VARCHAR(10) NOT NULL,

  birthYear INT NOT NULL,

   ... 등등 자신의 회원 테이블에 맞게 설정

 modType CHAR(6)             -- 변경된 타입 / 'UPDATE' or 'DELETE'

 modDate DATE,                  -- 변경된 날짜

 mod User VARCHAR(256)  -- 변경한 사용자

);

2) UPDATE 트리거 / DELETE 트리거 생성

DROP TRIGGER IF EXISTS backUserTbl_UpdateTrg;

DELIMITER //

CREATE TRIGGER backUserTbl_UpdateTrg

    AFTER UPDATE

    ON userTBL

    FOR EACH ROW

BEGIN

    INSERT INTO backup_userTbl VALUES( OLD.userID, OLD.name, ~등등 자신의 회원테이블~, 'UPDATE', CURDATE(), CURRENT_USER() );

END //

DELIMITER ;

 

DROP TRIGGER IF EXISTS backUserTbl_DeleteTrg;

DELIMITER //

CREATE TRIGGER backUserTbl_DeleteTrg

    AFTER DELETE

    ON userTBL

    FOR EACH ROW

BEGIN

    INSERT INTO backup_userTbl VALUES( OLD.userID, OLD.name, ~등등 자신의 회원테이블~, 'DELETE', CURDATE(), CURRENT_USER() );

END //

DELIMITER ;

 

2. BEFORE 트리거

값이 입력될 때, 출생년도의 데이터를 검사해서 데이터에 문제가 있으면 값을 변경시켜서 입력시킨다.

DROP TRIGGER IF EXISTS userTbl_BeforeInsertTrg;

DELIMITER //

CREATE TRIGGER userTbl_BeforeInsertTrg

    BEFORE INSERT

    ON userTBL

    FOR EACH ROW

BEGIN

    IF NEW.birthYear < 1900 THEN

        SET NEW.birthYear = 0;

    ELSEIF NEW.birthYear > YEAR(CURDATE()) THEN

        SET NEW.birthYear = YEAR(CURDATE());

    END IF;

END //

DELIMITER ;