Event & Trigger

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

절차형 SQL (Procedural SQL) 개념

일반적인 개발 언어처럼 SQL 언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.

종류

종류	설명
프로시저 (Procedure)	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
사용자 정의 함수 (User-Defined Function)	일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거 (Trigger)	DB 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

이번에는 이벤트와 트리거에 대해서만 다룰 예정이며, 프로시저와 사용자 정의 함수는 PostgreSQL에서 다루겠습니다.

---

Event

Event는 DB에서 특정 조건에 실행되는 스케쥴링이다.

💡 Event는 MySQL과 PostgreSQL(확장 프로그램 필요)에서 지원하는 기능이다.

---

Event 생성

실행할 쿼리가 1개일 때

CREATE EVENT archive_old_movies  -- 이벤트 이름
ON SCHEDULE EVERY 2 MINUTE  -- 2분마다 실행
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE  -- 현재 시간으로 부터 2분 뒤 이벤트 실행
DO  -- 실행할 쿼리
	INSERT INTO archived_movies
	SELECT * FROM movies
	WHERE release_date < YEAR(CURDATE()) - 20;  -- 20년 전에 개봉한 영화

실행할 쿼리가 2개 이상일 때

이벤트를 통해 실행할 쿼리에 ; 가 여러개 있으면 SQL이 처리를 할 때 문제가 발생할 수 있으므로 DELIMITER를 통해 이벤트 선언 시 구문문자를 다른 것으로 변경할 필요가 있다.

DELIMITER $$  -- DELIMITER가 없으면 쿼리 내부의 ;를 보고 SQL문이 끝날 수 있다.
CREATE EVENT archive_old_movies
ON SCHEDULE EVERY 2 MINUTE
    STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO BEGIN  -- 쿼리가 2개 이상일 경우 BEGIN ~ END 를 사용한다.
    INSERT INTO archived_movies
    SELECT * FROM movies
    WHERE release_date < YEAR(CURDATE()) - 20;  -- 쿼리 내부에선 ; 세미콜론을 사용한다.
    
    DELETE FROM movies WHERE release_date < YEAR(CURDATE()) - 20;
END$$  -- 이벤트 생성문이 끝날 때 설정한 DELIMITER를 붙인다.
DELIMITER ;  -- 이벤트를 생성하고 나서 DELIMITER를 $$ 에서 ; 로 원상복구해야 한다.

---

Event 삭제

DROP EVENT IF EXISTS archive_old_movies;

---

Trigger

어떤 조건이 실행되는 특정 시점에 함수를 실행하게 해 준다.

CREATE TRIGGER {trigger_name} 
[BEFORE / AFTER] [INSERT, UPDATE, DELETE] 
ON {table_name} 
FOR EACH ROW 
{query}

트리거가 실행될 때 어떤 종류의 데이터에 접근할 건지 [ NEW, OLD ] 키워드를 통해 정할 수 있다.

구분	설명
NEW	INSERT로 인해 새로 추가된 데이터에 접근 UPDATE로 인해 새로 변경된 데이터에 접근
OLD	DELETE로 인해 제거된 데이터에 접근 UPDATE로 인해 변경되기 전의 데이터에 접근

•  

---

Trigger 확인

SHOW TRIGGERS;

---

BEFORE Trigger

row가 INSERT, UPDATE, DELETE 되기 전 command 실행

실행할 쿼리가 1개 일 때

CREATE TRIGGER before_breed_insert
    BEFORE INSERT 
    ON breeds 
    FOR EACH ROW  -- breeds 테이블의 모든 열에 대하여 INSERT가 실행 되기 전
        INSERT INTO logs (changes)  -- logs 테이블의 changes 컬럼에
        VALUES (CONCAT('INSERT : ', NEW.name));  -- 새로 추가된 데이터의 name 레코드를 저장

실행할 쿼리가 2개 이상일 때

DELIMITER $$  -- SQL이 Trigger가 실행할 쿼리를 제대로 인식하도록 DELIMITER 사용
CREATE TRIGGER before_breed_insert
    BEFORE INSERT
    ON breeds
    FOR EACH ROW
    BEGIN  -- EVENT때 와 마찬가지로 BEGIN ~ END를 사용
        INSERT INTO logs (changes)
        VALUES (CONCAT('INSERT : ', NEW.name));
    
        SELECT * FROM logs;
    END$$
DELIMITER ;

---

AFTER Trigger

row가 INSERT, UPDATE, DELETE 된 후 command 실행

실행할 쿼리가 1개 일 때

CREATE TRIGGER after_breed_insert
    AFTER DELETE
    ON breeds
    FOR EACH ROW
        INSERT INTO logs (changes)
        VALUES (CONCAT('DELETE : ', OLD.name));

실행할 쿼리가 2개 이상일 때

DELIMITER $$
CREATE TRIGGER after_breed_insert
    AFTER DELETE
    ON breeds
    FOR EACH ROW
    BEGIN
        INSERT INTO logs (changes)
        VALUES (CONCAT('DELETE : ', OLD.name));
        
        -- 쿼리 추가 입력
    END$$
DELIMITER ;

심화 - 원하는 column에 대한 변경점을 로그에 남길 때

DELIMITER $$
CREATE TRIGGER after_breed_update
    AFTER UPDATE
    ON breeds
    FOR EACH ROW
    BEGIN
        DECLARE changes TINYTEXT DEFAULT '';    -- 변수 생성

        IF NEW.name != OLD.name THEN   -- NEW와 OLD를 사용하여 변경된 column 찾기
            -- 변경된 부분이 있을 경우 changes 변수에 문자열 추가
            SET changes = CONCAT(changes, '[Name: ', OLD.name, ' -> ', NEW.name, ']\\n');
        END IF;

        IF NEW.typical_lifespan != OLD.typical_lifespan THEN
            SET changes = CONCAT(changes, '[LifeSpan: ', OLD.typical_lifespan, ' -> ', NEW.typical_lifespan, ']\\n');
        END IF;

        IF NEW.size_category != OLD.size_category THEN
            SET changes = CONCAT(changes, '[SizeCategory: ', OLD.size_category, ' -> ', NEW.size_category, ']\\n');
        END IF;

        INSERT INTO logs (changes) VALUES (changes);
    END$$
DELIMITER ;

---

Trigger 삭제

DROP TRIGGER after_breed_insert