[MariaDB] 트리거

트리거란?

INSERT, UPDATE, DELETE 작업 수행될 때 자동으로 실행되는 SQL 코드 블록

※ SELECT는 데이터를 변경하지 않기 때문에 트리거의 대상이 될 수 없습니다.

1) 전체 트리거 조회

SHOW TRIGGERS\G;

 

2) 트리거 생성

※ 트리거에서 사용되는 테이블은 기존에 생성되어 있어야 합니다.

 

CREATE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name 

FOR EACH ROW

▷ 각 행마다 실행

BEGIN

    -- 트리거가 실행할 SQL 문들

    -- ex) INSERT INTO log_table (log_data) VALUES (NEW.column_name);

END;

ex) 

Step 1. 테이블 생성

CREATE TABLE logs (

    num INT AUTO_INCREMENT PRIMARY KEY,

    table_name VARCHAR(20),

    value VARCHAR(50),

    action VARCHAR(10),

    timestamp TIMESTAMP

    ▷ TIMESTAMP : 날짜/시간 저장 데이터타입

);

 

Step 2. 트리거 생성

(a) user_info 테이블에 INSERT 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER user_insert_trigger

AFTER INSERT ON user_info

FOR EACH ROW

BEGIN

    INSERT INTO logs (table_name, value, action, timestamp)

    VALUES ('user_info', NEW.name, 'INSERT', NOW());

    ▷ NOW() : 현재 시간 함수

END //

DELIMITER ;

※ INSERT(삽입) 시 NEW, DELETE(삭제) 시 OLD 키워드를 사용해야 합니다.

 

(b) team_info 테이블에 INSERT 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER team_insert_trigger

AFTER INSERT ON team_info

FOR EACH ROW

BEGIN

    INSERT INTO logs (table_name, value, action, timestamp)

    VALUES ('team_info', NEW.name, 'INSERT', NOW());

END //

DELIMITER ;

 

(c) user_info 테이블에 delete 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER user_delete_trigger

AFTER DELETE ON user_info

FOR EACH ROW

BEGIN

    INSERT INTO logs (table_name, value, action, timestamp)

    VALUES ('user_info', OLD.name, 'DELETE', NOW());

END //

DELIMITER ;

 

(d) team_info 테이블에 delete 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER team_delete_trigger

BEFORE DELETE ON team_info

FOR EACH ROW

BEGIN

    DECLARE done INT DEFAULT FALSE;

    DECLARE user_name VARCHAR(20);

    DECLARE cur CURSOR FOR SELECT name FROM user_info WHERE team_id = OLD.team_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP

        FETCH cur INTO user_name;

        IF done THEN

            LEAVE read_loop;

        END IF;

        INSERT INTO logs (table_name, value, action, timestamp)

        VALUES ('user_info', user_name, 'DELETE', NOW());

    END LOOP;

    CLOSE cur;

    INSERT INTO logs (table_name, value, action, timestamp)

    VALUES ('team_info', OLD.name, 'DELETE', NOW());

END //

DELIMITER ;

 

(e) user_info 테이블에 update 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER user_update_trigger

AFTER UPDATE ON user_info

FOR EACH ROW

BEGIN

    DECLARE changes TEXT DEFAULT '';

    ▷ changes 변수 선언

    IF NOT (OLD.name <=> NEW.name) THEN

    ▷OLD.name과 NEW.name 비교

    ▷ <=> : NULL-safe 비교 연산자로 NULL 값을 포함한 값 비교

        SET changes = CONCAT(changes, 'name: ', OLD.name, ' -> ', NEW.name);

        ▷ changes 변수에 name: OLD.name  -> NEW.name 저장

    END IF;

    ▷ IF 문 종료    

    IF NOT (OLD.age <=> NEW.age) THEN

        SET changes = CONCAT(changes, 'age: ', OLD.age, ' -> ', NEW.age);

    END IF;    

    IF NOT (OLD.email <=> NEW.email) THEN

        SET changes = CONCAT(changes, 'email: ', OLD.email, ' -> ', NEW.email);

    END IF;    

    IF NOT (OLD.team_id <=> NEW.team_id) THEN

        SET changes = CONCAT(changes, 'team_id: ', OLD.team_id, ' -> ', NEW.team_id);

    END IF;    

    IF changes != '' THEN

    ▷changes 변수 값 변경되었을 경우 수행

        INSERT INTO logs (table_name, value, action, timestamp)

        VALUES ('user_info', changes, 'UPDATE', NOW());

    END IF;

END //

DELIMITER ;

 

ex) UPDATE user_info SET age = 26 WHERE num = 9;

 

(e) team_info 테이블에 update 작업 수행 시 logs 테이블에 INSERT SQL문 실행

DELIMITER //

CREATE TRIGGER team_update_trigger

AFTER UPDATE ON team_info

FOR EACH ROW

BEGIN

    DECLARE changes TEXT DEFAULT '';

    IF NOT (OLD.team_id <=> NEW.team_id) THEN

        SET changes = CONCAT(changes, 'team_id: ', OLD.team_id, ' -> ', NEW.team_id);

    END IF;

    IF NOT (OLD.name <=> NEW.name) THEN

        SET changes = CONCAT(changes, 'name: ', OLD.name, ' -> ', NEW.name);

    END IF;

    IF changes != '' THEN

        INSERT INTO logs (table_name, value, action, timestamp)

        VALUES ('team_info', changes, 'UPDATE', NOW());

    END IF;

END //

DELIMITER ;

 

ex) UPDATE team_info SET name = 'ORANGE' WHERE team_id = 1;

 

3) 특정 트리거 정의 확인

SHOW CREATE TRIGGER trigger_name; 

 

※ 트리거는 수정할 수 없습니다.

※ 트리거 이름은 변경할 수 없습니다.

 

4) 트리거 삭제

DROP TRIGGER trigger_name;