[SQL] 자동실행 기능: Events / Triggers

Event 

데이터베이스가 해야 할 작업들을 스케줄링(일정한 주기로 자동으로 실행) 

미래의 특정 시간에 실행 

ex) 매주 수요일 오후 3시에 특정 조건에 해당되는 row를 삭제 

CREATE EVENT event_name
  ON SCHEDULE EVERY 1 DAY   - 매일 1번씩 실행 
  DO
    DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY;  -
    - 30일 지난 로그데이터는 삭제

CREATE EVENT backup_event
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '00:00:00')
DO
  INSERT INTO backup_table SELECT * FROM main_table;
-- 매일 자정에 main_table을 backup_table로 복사(백업)

 

CREATE TABLE archived_movies LIKE movies;
-- movies 테이블의 구조를 그대로 복사하여 archived_movies라는 새로운 테이블을 생성 
-- 원본 테이블의 스키마(구조)만 복사, 데이터는 복사 X: 주로 백업용 테이블을 만들 때 
-- 원본 테이블의 인덱스(primary key, unique, index 등)도 그대로 복사
-- foreign key는 복사되지 않음 

DROP TABLE archived_movies;     -- 테이블 전체 삭제 
TRUNCATE TABLE archived_movies; -- 테이블 구조는 남겨둔 채 데이터만 삭제 


DELIMITER //  -- 구문 끝내는 문자로 //를 사용 
CREATE EVENT archive_old_movies
  ON SCHEDULE EVERY 2 MINUTE                    -- 매 2분마다 실행  
  STARTS CURRENT_TIMESTAMP + INTERVAL 2 MINUTE  -- 현재시각부터 2분 후에 첫 실행 
  DO
  BEGIN
    INSERT INTO archived_movies 
    SELECT * FROM movies                        -- 모든 영화들을 가져온다 
    WHERE release_date < YEAR(CURDATE()) - 20;  -- 개봉날짜가 20년 전보다 작은 
  
    DELETE FROM movies WHERE release_date < YEAR(CURDATE()) - 20;  -- 옮겨진 데이터는 원본 테이블(movies)에서 삭제 
  END //

DELIMITER ;  -- 원래 사용하던 DELIMITER로 돌려놓음

 

모든 Event Scheduling 옵션 

https://chatgpt.com/share/d5844020-c1d8-46d9-a363-879f3421750f

---

Trigger 

특정 테이블에서 변경이 발생하면 즉시 반응 

실시간 데이터 변경 처리가 필요할 때 

한 번 실행되면 취소 불가 

ex) INSERT, UPDATE, DELETE와 같은 작업이 실행될 때 자동 실행 

ex) logs 테이블: 영화 DB에서 이뤄진 모든 변동사항을 기록 & 로그를 보관 

-- 회원가입 시 자동으로 가입날짜 설정하기 
CREATE TRIGGER set_signup_date
  BEFORE INSERT ON users
  FOR EACH ROW
  BEGIN
    SET NEW.signup_date = NOW();
  END;
-- users 테이블에 새 회원이 추가되면 signup_date 칼럼이 NOW()로 설정

-- 주문 삭제 시, 삭제 로그 기록 
CREATE TRIGGER log_order_deletion
  AFTER DELETE ON orders     -- orders 테이블에서 주문이 삭제되면 
  FOR EACH ROW
  BEGIN
    INSERT INTO order_logs (order_id, deleted_at)
    VALUES (OLD.id, NOW());
  END;
  -- order_logs 테이블에 삭제된 주문의 ID와 삭제시간을 저장

 

-- 데이터베이스의 모든 변경사항을 추적하는 기록 데이터베이스 만들기 
CREATE TABLE records (
  record_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  changes TINYTEXT,
  create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL) 


-- movies 테이블에 새 레코드가 삽입되기 전에 records 테이블에 변경 내용을 기록
CREATE TRIGGER before_movie_insert
  BEFORE INSERT ON movies  -- movies 테이블에 새로운 레코드가 삽입되기 직전에 실행 
  FOR EACH ROW 
  INSERT INTO records (changes) VALUES (CONCAT('Will insert: ', NEW.title));    
  -- records 테이블의 changes 칼럼에 "Will insert + 영화제목"을 기록 


INSERT INTO movies SELECT * FROM archived_movies WHERE movie_id = 1; 
-- archived_movies 테이블에서 movie_id = 1인 영화를 가져와 movies 테이블에 삽입

 

-- 칼럼별로 원하는 형태로 트리거 만들기 
DELIMITER // 
CREATE TRIGGER after_movie_update 
  AFTER UPDATE ON movies  -- movies 테이블에서 데이터가 수정된 후 실행되는 트리거 
  FOR EACH ROW 
  BEGIN 
    DECLARE changes TINYTEXT DEFAULT '';  -- changes 라는 변수 만들기 
    
    IF NEW.title <> OLD.title THEN 
      SET changes = CONCAT('title changed ', OLD.title, '-> ', NEW.title, '\n');
    END IF; 
    -- title 변경 감지
    
    IF NEW.budget <> OLD.budget THEN 
      SET changes = CONCAT(changes, 'Budget changed ', OLD.budget, '-> ', NEW.budget);
    END IF;
    -- budget 변경 감지 
    
    INSERT INTO records (changes) VALUES (changes);
    -- 모든 변경 사항을 records 테이블에 삽입 
    
  END // 
DELIMITER ;